Skip site navigation (1) Skip section navigation (2)

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

From: "Phil Endecott" <spam_from_postgresql_bugs(at)chezphil(dot)org>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #3002: PQexecParams only supports some commands;needs improved error reporting, documenting or fixing
Date: 2007-02-14 11:49:08
Message-ID: 1171453748934@dmwebmail.belize.chezphil.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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.











In response to

pgsql-bugs by date

Next:From: Filippo TosoDate: 2007-02-14 12:49:43
Subject: Re: BUG #2993: The program "postgres" is needed by initdb but was not found ...
Previous:From: Magnus HaganderDate: 2007-02-14 11:04:58
Subject: Re: BUG #2998: Installation Failed due to Permissions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group