PostgreSQL Hacks - proposal - plpgsql: execute using into

Author

proposal - plpgsql: execute using into

Pavel Stehule

2006-03-27, 8:28 pm

Hello

Current EXECUTE statemtn doesn't support other way for parametrisation than
concating strings. It works well but it's little bit unreadable. Oracle's
statement EXECUTE has positional replacement feature. It works similar our
RAISE statement (when position holder is %). EXECUTE position holder has
form :xxxx. xxxx has only symbolic value and isn't used for anything. Syntax
of enhanced statements is:

EXECUTE 'format string' USING expr_list

There are some problems about replacing string values in the SQL string.
Sometimes we have to enclose value between spaces or others symbols
(apostrophe or double apostrophe), sometimes not. Possible rules:
a) if position holder is inside string or identifier we don't enclose
value;
b) else numeric values are enclosed spaces and others (non regclass)
single apostrophes
c) regclass's values are enclosed douple apostrophes.

PL/pgSQL knows three dynamic statements. All will be enhanced.

Some examples:

EXECUTE 'SELECT :name||:sp||:surname
' USING 'Pavel',' ','Stehule';
EXECUTE e'SELECT ':name :surname' USING 'Pavel','Stehule';
EXECUTE 'SELECT * FROM :tabname' USING 'xb'::regclass;
EXECUTE 'SELECT * FROM ":base:num" USING 'mytab',1;

You can test it. I sent patch to pg_patches.

I invite any comments

Pavel Stehule

____________________
____________________
____________________
_____
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2006-03-27, 8:28 pm

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> Current EXECUTE statemtn doesn't support other way for parametrisation than
> concating strings. It works well but it's little bit unreadable. Oracle's
> statement EXECUTE has positional replacement feature.
> ...
> There are some problems about replacing string values in the SQL string.

Doesn't the Oracle implementation already imply a solution to that?

The examples you give look to me like they are escaping problems waiting
to happen, especially in view of the upcoming change in default
backslash behavior, so this whole thing makes me feel pretty nervous.
I think we'd be best off to leave EXECUTE alone, at least until we've
converged to the point where almost nobody is using non-standard-compliant
strings.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Pavel Stehule

2006-03-28, 7:30 am

> > There are some problems about replacing string values in the SQL string.
>
>Doesn't the Oracle implementation already imply a solution to that?
>

I don't know. I didn't find any detail documentation about it. I don't know
what Oracle exactly do.

>I think we'd be best off to leave EXECUTE alone, at least until we've
>converged to the point where almost nobody is using non-standard-compliant
>strings.
>

Maybe, but patch have to solve SQL string and non SQL strings too

Regards
Pavel Stehule

____________________
____________________
____________________
_____
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/

---------------------------(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

Sponsored Links