Re: Transaction Newbie

In article <a05111b08b9a30834d223(at)[192(dot)168(dot)1(dot)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.