From: Lee Harr <missive@frontiernet.net>
Subject: Re: Transaction Newbie
Date: Tue, 10 Sep 2002 23:27:06 +0000 (UTC)
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 42
Message-ID: <allv49$26cp$1@news.hub.org>
References: <a05111b08b9a30834d223@[192.168.1.20]>
Reply-To: missive@hotmail.com
User-Agent: slrn/0.9.7.3 (FreeBSD)
To: pgsql-sql@postgresql.org

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.