CODEWITHSUNDEEP
phppostgresql
willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477607

`pg_query_params` and too many parameters

One can query a database with the pg_query_params method, for instance:

pg_query_params($connection, 'SELECT foo FROM bar WHERE id=$1 AND name=$2',array("Foobar","Qux"));

However when the query doesn't use all parameters, for instance with the SELECT foo FROM bar WHERE id=$1 query, one receives the following error:

Query failed: ERROR: bind message supplies 2 parameters, but prepared statement "" requires 1

Why is pg_query_params so restrictive? One could "construct" the query and simply add tests dynamically. Can one "turn off" this behavior? What are the alternatives to offer a query that may (not) use less parameters than the provided once?

Upvotes: 2

Views: 662

Answers (1)

byrnedo
byrnedo

Reputation: 1475

It is probably just wrapping the prepared statement syntax in postgres.

PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

And as such will just catch the exception thrown by postgres if it's executing your statement with that extra variable.

Postgresl Prepare Documentation

Edit: From having a look around it seems to call PQExecParams from the postgresql C library, libpq. http://www.postgresql.org/docs/9.1/static/libpq-exec.html#LIBPQ-PQEXECPARAMS

If you really need pg_query_params to take surplus arguments, why not just write a wrapper to preg_match the $[0-9] strings in the query and only supply the total amount of matches to the pg_query_params function.

For example:

function my_query_params( $conn, $query, $params )
{
     $matches = array();
     preg_match( '/[^\'"]\$[0-9]+[^\'"]/', $query, $matches );

     $params = array_slice( $params, 0, count( $matches ) - 1 );

     return pg_query_params( $conn, $query, $params );
}

I didn't test this at all but the idea is there.

Upvotes: 1

Related Questions