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?

Answer 1

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.