Postgres/PHP - What is the standard way to retrieve the ID of an inserted row?

I have searched on Google and read a couple of articles on how different people approach this problem, but I was wondering what the standard way of solving it is and also, which one will work best for my situation.

I have an AJAX page that creates a new question开发者_如何学JAVA and I need to know how to retrieve the ID from the insert query within the same php file, on the next line.

It looks something like this:

$r = pg_query("INSERT INTO questions (audit_id, type_id, order) VALUES (1,1,1)");
// Fetch ID from $r here...

I have seen the mysql_insert_id() function for MySQL and heard that pg_last_oid() is similar for PostgreSQL, but the documentation claims that it is deprecated and will be removed soon. I have also seen the use of CURRVAL('my_sequence_table.id') but I'm not sure if this will work with the AJAX since that might raise a race condition.

Can somebody please tell me the standard PHP/PostgreSQL way to solve this problem? I would greatly appreciate any comments.

P.S. I miss Ruby on Rails!

Probably your best bet is to use INSERT INTO questions ... VALUES (1,1,1) RETURNING audit_id, as that will give you the right value whether you plug the value manually or through a sequence.

Note that the currval() trick should certainly work if you get the same session -- currval() is guaranteed to return the same value that the sequence delivered to your session, regardless of what other concurrent sessions are doing. It could only cause a problem if you have a connection pooler that somehow uses a different connection for the first query than the second query, but it would be quite broken a pooler if it did that. I know of no pooler that does things that way.

Update: See the pg_get_serial_sequence() function, which takes a table and column name and returns the associated sequence name. It is more practical to use than hardcoding the sequence name in your code.

MySQL has the notion of autoincrement fields, PostgreSQL has the notion of sequences. A Postgres sequence is a named database object whose value can be increased. Please see this FAQ.

I would recommend using the CURRVAL('my_sequence_table.id') option.

It shouldn't cause a race condition for you as it will return the latest value for the current session and these will be two consecutive statements in the same session.