postgresql nextval question on sequences

Question

I am trying to work with postgresql 'nextval' in PHP. How can I fill in the parenthesis in the third line in order to replace TXN_ID with the value of nextval('schemadb.audit_txn_seq')?

$DB->query("SELECT nextval('schemadb.audit_txn_seq')");
$DB->query('SET CONSTRAINTS ALL DEFERRED');
$DB->query('SELECT schemadb.undo_transaction(TXN_ID)');

Thanks!

Answer 1

You did not tell us what is $DB variable. I guess you are using PDO.

The question "Is there anyway to store the result of query into php variable" is kind of beginner question, and is easilly answered in the manual.

You must understand, that (from PHP's point of view) there is no difference between

SELECT nextval('schemadb.audit_txn_seq')

and

SELECT xcolumn FROM xtable LIMIT 1

If you want to fetch data values from ANY query, you do it always the same, standard way:

1. query
2. execute
3. fetch

Hope that helps.

Answer 2

Try:

$DB->query("SELECT nextval('schemadb.audit_txn_seq')");
$DB->query('SET CONSTRAINTS ALL DEFERRED');
$DB->query("SELECT schemadb.undo_transaction( currval('schemadb.audit_txn_seq') )");

Answer 3

I'm not sure about the interaction with SET CONSTRAINTS ALL DEFERRED, but have you tried doing this:

SELECT schemadb.undo_transaction(nextval('schemadb.audit_txn_seq'))

The question is now what does "undo_transaction" return? If it returns the transaction ID upon successful completion, then you're good to go!