[BUGS] BUG #3002: PQexecParams only supports some commands; needs improved error reporting, documenting or fixing
The following bug has been logged online: Bug reference: 3002 Logged by: Phil Endecott Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.0 Operating system: All? Description:PQexecParams only supports some commands; needs improved error reporting, documenting or fixing Details: 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. Three fixes occur to me: 1. An error could be reported immediately if an unsupported command is passed to PQexecParams. 2. The documentation of PQexecParams could be fixed to list the four commands to which it can be applied. 3. PQexecParams could be extended to support these commands. I have written a simple wrapper for PQexecParams that expands and escapes parameters and calls PQexec, for a small set of parameter types. (Please ask me if you'd like to see the code; it is only 30 lines of obviousness.) Presumably this would be even simpler if done in the backend. Thanks, Phil. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Fwd: Re: [BUGS] 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" <[EMAIL PROTECTED]> 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 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.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #3002: PQexecParams only supports some commands; needs improved error reporting, documenting or fixing
Hi Tom,
Tom Lane wrote:
"Phil Endecott" <[EMAIL PROTECTED]> 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 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.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
