Re: Transaction Newbie

In article <a05111b08b9a30834d223@[192.168.1.20]>, Michelle Murrain wrote:
> Hi,
> 
> I've been using Postgres for a while, almost exclusively through the 
> perl DBI (although I do plenty of work on the command line).
> 
> I have realized, belatedly, that I need transactions for this thing I 
> want to accomplish, but I've not done transactions before, so I need 
> a bit of help. And, I'm not sure whether it's a transaction I need, 
> or a lock.
> 
> I have (many) tables with automatically entering serial value as 
> primary key, set by a sequence. I need to insert a row, and then get 
> the value of that row I just entered. I thought first of doing two 
> sql statements in a row:
> 

It sort of depends on what you mean by "get the value of that row".

If you just need the primary key value, then yes you can do:

insert into t (c1, c2, c3) values (v1, v2, v3);
select currval('sequence_name');

Or if what you really want is to use that primary key value to
insert more records in to other tables which relate to this one
(ie with a foreign key), then what you might want is:

-- pk here inserted automatically by DEFAULT nextval('sequence_name')
insert into t (c1, c2, c3) values (v1, v2, v3);

insert into t2 (fk, c4, c5) values (currval('sequence_name'), v4, v5);


As long as the two statements are in the same session (ie. in
the same connection instance) then there is no need for a transaction.

Sequences are set up to be used this way, currval() sees only values
retrieved by nextval() in *this session* it will not see anything
done in other connections to the database.