How can one determine the pkey of an inserted row after using pg_insert()?

Question

I need to be able to identify the row I just inserted after using pg_insert, but the doc just says that it returns true/false. How can I determine the pkey of the record from a pg_insert()? I know i could use a hand crafted query with a "RETURNING pkey" at the end, but I don't want to use the pg_query() because pg_insert automatically escapes everything for you so I want to use it instead.

Answer 1

You can use pg_query_params with a RETURNING clause and use parameters to escape everything.

The only problem with this function is that, when an error occurs, you cannot know the real cause of the problem. Getting correct error message means using pg_send_query_params with pg_get_result.

See a code example.

Answer 2

Since there are no id concept in pgsql (only sequences), you can use the following code.

SELECT currval(pg_get_serial_sequence(tablename, 'id'));