From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | "Williams, Travis L, NPONS" <tlw(at)att(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to obtain serial generate when doing an insert? |
Date: | 2003-05-27 20:52:31 |
Message-ID: | Pine.LNX.4.33.0305271447110.21159-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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...
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2003-05-27 21:03:50 | Re: Postgresql on SUN Server |
Previous Message | Williams, Travis L, NPONS | 2003-05-27 20:44:43 | How to obtain serial generate when doing an insert? |