Executing prepared statements via bind params

From: Peter Bex <Peter(dot)Bex(at)xs4all(dot)nl>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Executing prepared statements via bind params
Date: 2011-06-16 19:52:29
Message-ID: 20110616195229.GE17159@frohike.homeunix.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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

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

"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


Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2011-06-16 20:05:03 Re: Installing Fedora 15 hosed my db...
Previous Message Scott Marlowe 2011-06-16 19:44:47 Re: PostgreSQL 8.4.8 bringing my website down every evening