postgresqlpostgresql-9.1lastinsertidsql-returningcurrval

How can I use PostgreSql's CURRVAL / RETURNING from another php file?

I'm running an INSERT query from one php file, but then I need to get the last inserted id from an external php file so as to run another query based on that id. How can I pull this off using CURRVAL or RETURNING? From the external php file, if I do something like

$result = pg_query($db,"SELECT CURRVAL('app.example_id_seq'), ... "); I get no results.

Solution

  • You can query the sequence as if it were a table:

    SELECT last_value
FROM app.example_id_seq
WHERE is_called;

    This will return no result if the sequence has never been used (then is_called is FALSE).

    But this is a bad value to determine the ID of a newly inserted table row. It will only work if nobody else has used the sequence after the row was inserted. Also, you cannot figure out if the insertion failed or not.