Re: [PATCHES] EXECUTE USING for plpgsql (for 8.4)
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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [PATCHES] EXECUTE USING for plpgsql (for 8.4)
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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] EXECUTE USING for plpgsql (for 8.4)
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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] EXECUTE USING for plpgsql (for 8.4)
2007/10/23, Heikki Linnakangas [EMAIL PROTECTED]:
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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] EXECUTE USING for plpgsql (for 8.4)
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 [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
