Postgresql question

trooper

Is there a function in postgresql to pull the id of a record you have just put in.

Scenario:

I am entering in records to a table, i need to get the id of the record just added. I can't use MAX(id) as there could easily be multiple people adding at the same time.

Any ideas

Cheers

Gary

Anon

http://www.php.net/manual/en/function.pg-getlastoid.php

is what you're looking for. Every row in a postgresql database has a unique OID (object ID) which is assigned to it. So, by doing something like this:

$res = pg_exec($conn,"insert into table values ('value','value',number);
$oid = pg_getlastoid($res);
$res2 = pg_exec($conn,"select id from table where oid=$oid");
$id = pg_result($res2,0,"oid");

toma42

The OID used by Scott is a great method. Another would be to get an id from your sequence and insert it by hand rather than relying on a trigger for the primary key.

Anon

But note that this is a race condition looking to happen unless you use a transaction.

Better to use last oid, where you know the transaction will go through and let the triggers in postgres do the incrementing.