From: Lee Harr <missive@frontiernet.net>
To: pgsql-sql@postgresql.org
Subject: Re: Transaction Newbie
Date: Tue, 10 Sep 2002 23:27:06 +0000 (UTC)
Message-ID: <allv49$26cp$1@news.hub.org> (raw)
References: <a05111b08b9a30834d223@[192.168.1.20]>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: missive@frontiernet.net
Subject: Re: Transaction Newbie
In-Reply-To: <allv49$26cp$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox