CODEWITHSUNDEEP
phppostgresqlsequencenextval
littleK
littleK

Reputation: 20163

postgresql nextval question on sequences

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!

Upvotes: 1

Views: 7284

Answers (3)

Matthew Wood
Matthew Wood

Reputation: 16427

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!

Upvotes: 0

filiprem
filiprem

Reputation: 276

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.

Upvotes: 1

Justin Ethier
Justin Ethier

Reputation: 134255

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') )");

Upvotes: 0

Related Questions