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

Question

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.

Answer 1

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.

Answer 2

Not sure what that tree dots in your example are for. However:

select currval('app.example_id_seq'::regclass);

Should work. Sometimes it can fail with:

ERROR:  55000: currval of sequence "example_id_seq" is not yet defined in this session

So nothing called nextval before currval. But if you called insert into which worked, that currval must work as well.

To start using RETURNING, please visit docs - there are examples provided. Basically you are listing column names that should be in result of insert, or update.