On the topic of how to programatically get a just-inserted serial
value, I propose the Sqlflex model for adoption into postgresql.
In that model, the return protocol for INSERT is altered to return
the serial value of the just-inserted record IFF the input value
for the serial column was 0.  [Side rules: tables can only have one
serial column, and db-generated serial values are always natural
numbers.]  For example,

 create table mytable (id serial, name varchar);

 -- this returns # of rows inserted, as usual...
 insert into mytable (name) values ('John');

 -- this returns serial 'id' of inserted record...
 insert into mytable (id,name) values (0,'Mary');

This requires no syntax change to INSERT (a Good Thing),
and does not require any additional higher-level processing to
get the serial value.  We have had good success with this
approach on some relatively high-performance 7x24x365 dbs.

Presently, I am performing an additional select to get the same
effect (in perl DBI) immediately after $sth->execute() for the
original insert query, e.g.,

 select id from mytable where oid = $sth->{pg_oid_status}

Seems a waste to have to do this, but I'm not aware of another way.

-Ed





Wed, 03 Nov 1999 12:31:22 -0600
Ed Loehr <ELOEHR@austin.rr.com>
[HACKERS] getting new serial value of serial insert