Fwd: Re: BUG #3002: PQexecParams only supports some commands; needs improved error reporting, documenting or fixing

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(at)chezphil(dot)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.