Thread: EXECUTE USING for plpgsql (for 8.4)
Hello this patch add USING clause into plpgsql EXECUTE statements. Proposal: http://archives.postgresql.org/pgsql-hackers/2007-10/msg00790.php I found, so dynamics statements are little bit faster with parameters, because we don't need call lot of in out/in functions. Mainly it is barier to SQL injection. I have question, who will be commiter of plpgsql region? I am quite irritated from 8.3 process. Bruce's patch queue more or less black hole, and I have not any idea, if somebody checking my patches or not and if I have to be in readiness or not. Patch queue is longer and longer, and I need to know any responsible person who can be recipient of my reminder request. Really it's nothing nice, if your work is repeatedly deleted or inserted to current queue. Nobody can do any plans. Best regards Pavel Stehule
Attachment
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Pavel Stehule wrote:
> Hello
>
> this patch add USING clause into plpgsql EXECUTE statements.
>
> Proposal:
> http://archives.postgresql.org/pgsql-hackers/2007-10/msg00790.php
>
> I found, so dynamics statements are little bit faster with parameters,
> because we don't need call lot of in out/in functions. Mainly it is
> barier to SQL injection.
>
> I have question, who will be commiter of plpgsql region? I am quite
> irritated from 8.3 process. Bruce's patch queue more or less black
> hole, and I have not any idea, if somebody checking my patches or not
> and if I have to be in readiness or not.
>
> Patch queue is longer and longer, and I need to know any responsible
> person who can be recipient of my reminder request. Really it's
> nothing nice, if your work is repeatedly deleted or inserted to
> current queue. Nobody can do any plans.
>
> Best regards
> Pavel Stehule
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Pavel Stehule wrote: > Hello > > this patch add USING clause into plpgsql EXECUTE statements. > > Proposal: > http://archives.postgresql.org/pgsql-hackers/2007-10/msg00790.php > > I found, so dynamics statements are little bit faster with parameters, > because we don't need call lot of in out/in functions. Mainly it is > barier to SQL injection. FWIW, it looks pretty good to me. This doesn't work: create function exc_using(varchar) returns varchar as $$ declare v varchar; begin execute 'select upper($1)' into v using ('aa'); return v; end $$ language plpgsql; postgres=# SELECT exc_using('fooa'); ERROR: failed to find conversion function from unknown to text CONTEXT: SQL statement "select upper($1)" PL/pgSQL function "exc_using" line 3 at EXECUTE statement I also noted that the patch makes USING a keyword. Not sure if we care about that or not. > I have question, who will be commiter of plpgsql region? I am quite > irritated from 8.3 process. Bruce's patch queue more or less black > hole, and I have not any idea, if somebody checking my patches or not > and if I have to be in readiness or not. > > Patch queue is longer and longer, and I need to know any responsible > person who can be recipient of my reminder request. Really it's > nothing nice, if your work is repeatedly deleted or inserted to > current queue. Nobody can do any plans. All I can say is that I can feel your pain. Let's hope and do our best to make 8.4 smoother. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
>
> This doesn't work:
>
> create function exc_using(varchar) returns varchar
> as $$
> declare v varchar;
> begin
> execute 'select upper($1)' into v using ('aa');
it cannot work. Your parameter is row. But
into v using 'aaa' doesn't work too :(
ERROR: failed to find conversion function from unknown to text
CONTEXT: SQL statement "select upper($1)"
you have to specify type: use argument, variable or casting
.... using text 'aaa'; or select upper($1::text)
It is question for Tom. Why prepared statement cannot cast from literal to text
http://www.nabble.com/Blowback-from-text-conversion-changes-t3977711.html
>
> I also noted that the patch makes USING a keyword. Not sure if we care
> about that or not.
>
I am afraid to change well know syntax (SQL/PSM use it in same context too).
Pavel
Pavel Stehule wrote:
>> This doesn't work:
>>
>> create function exc_using(varchar) returns varchar
>> as $$
>> declare v varchar;
>> begin
>> execute 'select upper($1)' into v using ('aa');
>
> it cannot work. Your parameter is row.
Really? "execute 'select upper($1)' into v using ('aa'::varchar);"
works, as does "execute 'select $1 + 1' into v using (12345);".
> But into v using 'aaa' doesn't work too :(
>
> ERROR: failed to find conversion function from unknown to text
> CONTEXT: SQL statement "select upper($1)"
>
> you have to specify type: use argument, variable or casting
> .... using text 'aaa'; or select upper($1::text)
>
> It is question for Tom. Why prepared statement cannot cast from literal to text
> http://www.nabble.com/Blowback-from-text-conversion-changes-t3977711.html
Yeah, I suppose we'll just live with that. Using literals as arguments
is kind of pointless anyway, since you could as well put the literal in
the query as well and not bother with the USING.
>> I also noted that the patch makes USING a keyword. Not sure if we care
>> about that or not.
>>
> I am afraid to change well know syntax (SQL/PSM use it in same context too).
No I think the syntax is fine. I'm just wondering if it really has to be
a reserved keyword to implement that syntax. Looking at the plpgsql
grammar close, we don't categorize keywords like we do in the main
grammar, so maybe what I'm saying doesn't make any sense.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
2007/10/23, Heikki Linnakangas <heikki@enterprisedb.com>:
> Pavel Stehule wrote:
> >> This doesn't work:
> >>
> >> create function exc_using(varchar) returns varchar
> >> as $$
> >> declare v varchar;
> >> begin
> >> execute 'select upper($1)' into v using ('aa');
> >
> > it cannot work. Your parameter is row.
>
> Really? "execute 'select upper($1)' into v using ('aa'::varchar);"
> works, as does "execute 'select $1 + 1' into v using (12345);".
>
No, propably not. I am not sure, when Postgres grouping fields into
row. Problem is only in unknown literal.
> > But into v using 'aaa' doesn't work too :(
> >
> > ERROR: failed to find conversion function from unknown to text
> > CONTEXT: SQL statement "select upper($1)"
> >
> > you have to specify type: use argument, variable or casting
> > .... using text 'aaa'; or select upper($1::text)
> >
> > It is question for Tom. Why prepared statement cannot cast from literal to text
> > http://www.nabble.com/Blowback-from-text-conversion-changes-t3977711.html
>
> Yeah, I suppose we'll just live with that. Using literals as arguments
> is kind of pointless anyway, since you could as well put the literal in
> the query as well and not bother with the USING.
>
> >> I also noted that the patch makes USING a keyword. Not sure if we care
> >> about that or not.
> >>
> > I am afraid to change well know syntax (SQL/PSM use it in same context too).
>
> No I think the syntax is fine. I'm just wondering if it really has to be
> a reserved keyword to implement that syntax. Looking at the plpgsql
> grammar close, we don't categorize keywords like we do in the main
> grammar, so maybe what I'm saying doesn't make any sense.
>
yes, it's ok.
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
>