proposal - plpgsql: execute using into

From: "Pavel Stehule" <pavel(dot)stehule(at)hotmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: proposal - plpgsql: execute using into
Date: 2006-03-27 19:39:54
Message-ID: BAY20-F209756D26D9C0F8CE6001CF9D20@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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/

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-03-27 21:09:36 Re: proposal - plpgsql: execute using into
Previous Message Thomas Hallgren 2006-03-27 18:09:44 Re: Shared memory

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2006-03-27 20:01:05 Re: proposal - plpgsql: execute using into
Previous Message Pavel Stehule 2006-03-27 19:25:33 proposal - plpgsql: execute using into