CODEWITHSUNDEEP
phpdatabasepostgresql
codeassembly
codeassembly

Reputation: 1142

Is there any way to send more than one postgresql sql prepared statement in one call in php?

Is there any way to execute more sql prepared statements at once? Or at least use something to achieve this result, can it be emulated with transactions?

pg_send_query can execute more statements (from php docs "The SQL statement or statements to be executed.") but pg_send_execute and pg_send_prepare can work only with one statement.

The query parameter has the following description

"The parameterized SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not allowed.) If any parameters are used, they are referred to as $1, $2, etc."

from http://www.php.net/manual/en/function.pg-send-prepare.php

Is there any way to send more statements at once to make less roundtrips between php and postgresql like the pg_send_query does?

I don't want to use pg_send_query because without parameter binding I can have sql injection vulnerabilities in my code.

Upvotes: 3

Views: 1769

Answers (2)

Matthew Wood
Matthew Wood

Reputation: 16417

The round trips to the DB server shouldn't be your bottleneck as long as you are (a) using persistent connections (either directly or via a pool) and (b) aren't suffering from the "n+1 selects" problem.

New connections have an order of magnitude overhead which slows things down if done on every query. The n+1 problem results in generating far more trips than is really needed if the application retrieved (or acted upon) sets of related rows rather than doing all operations one at a time.

See: What is the n+1 selects problem?

Upvotes: 1

Maxem
Maxem

Reputation: 2684

Separate your queries by semicolon:

UPDATE customers SET last_name = 'foo' WHERE id = 1;UPDATE customers SET last_name = 'bar' WHERE id = 2;

Edit: Okay you cannot do this on the call side:

The parameterized SQL statement. Must contain only a single statement. (multiple statements separated by semi-colons are not allowed.)

Another way would be to call a stored procedure with this method and this SP issues multiple statements.

Upvotes: 0

Related Questions