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
