pg_prepare: cannot insert multiple commands into a prepared statement

Question

I have 2 tables, TableA and TableB. TableB has a fk field pointing to TableA.

I need to use a DELETE statement for TableA, and when a record in it is deleted I need to delete all records in TableB related to that record in TableA. Pretty basic.

begin;

DELETE FROM TableB
WHERE nu_fornecedor = $1;

DELETE FROM TableA
WHERE nu_fornecedor = $1;

commit;

This string is passed to pg_prepare(), but then I get error ERROR: cannot insert multiple commands into a prepared statement

Ok, but I need to run both commands in the same transaction, I cant execute 2 separated statements. I tried to use with without begin-commit and got same error.

Any idea how to do it?

It seems you're confusing statements and transactions. In one transaction you can execute as many statements as needed. In fact that's the point of having a transaction in the first place. — Daniel Vérité
By statement I mean a SQL statement prepared by 1 pg_prepare() to be executed by 1 pg_execute(). In this case, this statement has 2 commands to be executed, and they must be transactional. — Hikari
So for this need I can't use prepared statement and must use traditional pq_query()? — Hikari
You may pass multiple queries inside one pg_query call, it will work, but be aware that you may just as well open a transaction and do it in several queries. The changes will not be visible from outside the transaction until it commits. — Daniel Vérité

Chris Travers Chris Travers · Accepted Answer · 2013-12-03T03:17:41

To understand what is going on and your options, let me explain what a prepared statement is and what it is not. You can use pg_prepare, but only for the statements individually, not for the transaction as a whole.

A prepared statement is a statement handed to PostgreSQL which is then parsed for and stored as a parse tree for future use. On first execution, the parse tree is planned with the inputs provided, and executed, and the plan cached for future use. Usually it makes little sense to use prepared statements unless you want to reuse the query plan (i.e. executing a bunch of otherwise identical update statements hitting roughly the same number of rows), all in the same transaction.

If you want something that gives you the benefits of separating parameters from parse trees but does not cache plans, see pg_query_param() in the PHP documentation. That is probably what you want.

pg_prepare: cannot insert multiple commands into a prepared statement

1 Answers