[HACKERS] getting new serial value of serial insert

From: Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [HACKERS] getting new serial value of serial insert
Date: 1999-11-03 18:43:04
Message-ID: 38208238.A8320D4A@austin.rr.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Aaron J. Seigo 1999-11-03 19:21:00 Re: [HACKERS] Get OID of just inserted record
Previous Message Ed Loehr 1999-11-03 18:31:22 [HACKERS] getting new serial value of serial insert