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
