Re: Executing prepared statements via bind params

On Thu, Jun 16, 2011 at 05:19:41PM -0400, Tom Lane wrote:
> Peter Bex <Peter(dot)Bex(at)xs4all(dot)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