Re: How to obtain serial generate when doing an insert?

On Tue, 27 May 2003, Williams, Travis L, NPONS wrote:

> I have a table with a serial field, how can I obtain what serial was
> generated for the insert I'm doing?  I could just do another query for
> max(id) .. where id is my serial field.. but I'm worried another insert
> could have already came in.

There are a few ways to do this.  The "standard" way is pretty much this:

begin;
insert into table (field1,field2) values ('xxx','yyy');
select currval('seqname');
<more queries go here...>
commit;

OR

begin;
select nextval('seqname');
insert into table (field1,field2,id) values ('xxx','yyy',$valfromabove);
<more queries go here...>
commit;

You can also use OIDs, in some interfaces, the OID of the last inserted
row is availble, like libpq which is used by PHP and C.  no need for a
transaction:

$res = pg_query($conn,"insert into table ...");
$id = pg_last_oid($res);
$res2 - pg_query($conn,"select id from table where oid=$id");
print "Last inserted row oid is ";
print pg_result($res2,0,'id');

Since OIDs are optional, and may some day be deprecated for rows, it's not
the best way...