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

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(at)momjian(dot)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(at)enterprisedb(dot)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
>