From: Michael Fuhr <mike@fuhr.org>
To: PFC <lists@boutiquenumerique.com>
Cc: lmeadors@apache.org
Cc: pgsql-sql@postgresql.org
Subject: Re: getting back autonumber just inserted
Date: Thu, 7 Jul 2005 16:44:52 -0600
Message-ID: <20050707224452.GA50696@winnie.fuhr.org> (raw)
In-Reply-To: <op.stkdygr8th1vuj@localhost>
References: <42CD6B58.6060508@techevolution.be>
<20050707201433.94901.qmail@web41013.mail.yahoo.com>
<20050707203715.GA26528@winnie.fuhr.org>
<a10a823c05070713473add03d7@mail.gmail.com>
<20050707205725.GA28200@winnie.fuhr.org>
<op.stkdygr8th1vuj@localhost>
On Fri, Jul 08, 2005 at 12:26:30AM +0200, PFC wrote:
>
> >That's a different issue than whether currval() is subject to
> >interference from other transactions. And just wait until PostgreSQL
> >8.1 comes out and people start using lastval() -- then it could get
> >*really* confusing which sequence value you're getting.
>
> What happens if an INSERT trigger inserts something into another
> table which also has a sequence ?
Do you mean with lastval()? Here's what happens:
CREATE FUNCTION trigfunc() RETURNS trigger AS $$
BEGIN
INSERT INTO bar (x) VALUES (NEW.x);
RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE TABLE foo (id serial PRIMARY KEY, x integer);
CREATE TABLE bar (id serial PRIMARY KEY, x integer);
CREATE TRIGGER footrig BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE trigfunc();
ALTER SEQUENCE bar_id_seq RESTART WITH 50;
INSERT INTO foo (x) VALUES (12345);
SELECT lastval();
lastval
---------
50
(1 row)
SELECT * FROM foo;
id | x
----+-------
1 | 12345
(1 row)
SELECT * FROM bar;
id | x
----+-------
50 | 12345
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
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: mike@fuhr.org, lists@boutiquenumerique.com, lmeadors@apache.org
Subject: Re: getting back autonumber just inserted
In-Reply-To: <20050707224452.GA50696@winnie.fuhr.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