Re: getting back autonumber just inserted

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/