Thread: Executing prepared statements via bind params
Hi all, I'm seeing a bit strange (some might say inconsistent) behaviour, possibly a bug. First, I prepare a statement by running the query: "PREPARE bar (int) AS (SELECT 1, $1, 3)" Then I try to use the generic libpq query function PQsendQueryParams with a query of "EXECUTE bar(2)" and I get back a result set with one record (1, 2, 3) in it. This is fine. But when I try to do the same but pas the 2 as a parameter, (I do "EXECUTE bar($1)" with $1 bound to "2"), I get an error: ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0 This doesn't make much sense to me. It's the same error you get when trying to run a nonparameterized query like "SELECT 1" with $1 bound to anything. Does the query parser somehow miss the fact that there's a placeholder in the EXECUTE statement? I'm attempting to keep my Scheme library's API as small and simple as possible, so I'd like to avoid having a separate procedure for querying and one for executing prepared statements, considering there's also an SQL command for executing prepared statements. Is there a particular reason there are separate functions in libpq (aside from historical accident)? Cheers, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth
Peter Bex <Peter.Bex@xs4all.nl> writes:
> But when I try to do the same but pas the 2 as a parameter,
> (I do "EXECUTE bar($1)" with $1 bound to "2"), I get an error:
Why would you do that, rather than executing the prepared statement
directly with PQexecPrepared? Interposing an EXECUTE doesn't do
anything but add parsing overhead.
The reason this particular case doesn't work is that utility statements
(including EXECUTE) don't have any support for $n parameters.
regards, tom lane
On Thu, Jun 16, 2011 at 05:19:41PM -0400, Tom Lane wrote: > Peter Bex <Peter.Bex@xs4all.nl> writes: > > But when I try to do the same but pas the 2 as a parameter, > > (I do "EXECUTE bar($1)" with $1 bound to "2"), I get an error: > > Why would you do that, rather than executing the prepared statement > directly with PQexecPrepared? I'm writing a Scheme language binding and currently I simply don't have any bindings for this function, and a user of this library was experimenting with some optimizations for his code and ran into this. I was kind of hoping to avoid having too many special-purpose functions and since there's also an SQL "EXECUTE" function, PQexecPrepared seemed a bit redundant. > Interposing an EXECUTE doesn't do anything but add parsing overhead. Is this parsing overhead of an EXECUTE statement (which should be very short and simple) *that* significant compared to the savings you get when preparing a complex SQL statement which is executed many times? > The reason this particular case doesn't work is that utility statements > (including EXECUTE) don't have any support for $n parameters. Why not, is it simply something nobody ever needed? It seems rather logical to be able to replace any literal by an equivalent parameter in any SQL statement. I should probably add support for PQexecPrepared at some point but even then, as a user, I'd probably expect this to be possible for reasons of symmetry and regularity. It might also make it easier for certain types of generated SQL. Cheers, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth