Thread: Fwd: Re: BUG #3002: PQexecParams only supports some commands; needs improved error reporting, documenting or fixing
Fwd: Re: BUG #3002: PQexecParams only supports some commands; needs improved error reporting, documenting or fixing
From
"Phil Endecott"
Date:
I'm resending this as the first attempt didn't make it to the list. Phil. Phil Endecott wrote: > Hi Tom, > > Tom Lane wrote: >> "Phil Endecott" <spam_from_postgresql_bugs@chezphil.org> writes: >>> I understand that PQexecParams may only be used for SELECT, INSERT, DELETE >>> and UPDATE commands, but not for other commands where parameter substitution >>> might be useful such as CREATE VIEW and CREATE TABLE AS SELECT. When I used >>> PQexecParams with CREATE VIEW with one $ parameter I got no error >>> immediately, but got a "no value found for parameter 1" error when I later >>> ran a query that used the view. >> >> As near as I can tell, your issue is that you haven't thought carefully >> about what "$1" in a CREATE VIEW *means*. Is it supposed to represent a >> constant value bound into the view when it's created? > > Yes. > > I'm just relying on the documentation of PQexecParams, which says > things like: "the primary advantage of PQexecParams over PQexec is that > parameter values may be separated from the command string, thus > avoiding the need for tedious and error-prone quoting and escaping." > This is the benefit that I was hoping to get. I simply expected > PQexecParams to substitute the parameter values at the positions in the > command string where the $ placeholders were, or to behave as if that > is what it had done. > >> I'm not seeing a use-case that would justify work on this. > > In my IMAP mail server, I run a series of "create temporary view" > commands when the IMAP LOGIN command is processed. These views filter > the message database down to just that user's messages, so that > subsequent queries are simplified: > > 1 LOGIN phil password > ----> create temporary view u_messages as select * from messages where owner='phil' > > 2 SELECT Today > ----> select msg_id from u_messages where age(msgdate)<'1 day'::interval > > I have a C++ wrapper around libpq. This allows me to write things like: > > typedef std::string username_t; > Query<username_t> create_u_messages > ("create temporary view u_messages as select * from messages where owner=$1"); > .... > create_u_messages("phil"); > > As currently implemented, this uses PQexecParams. I will have to > change it to do parameter substitution itself and then call PQexec, > either unconditionally or after parsing enough of the command to > recognise whether it is supported by PQexecParams. > > > Regards, > > Phil.