proposal - plpgsql: execute using into

["Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com>]

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/