Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

pgsql-hackers by date

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

pgsql-patches by date

Next:From: Tom LaneDate: 2006-03-27 20:01:05
Subject: Re: proposal - plpgsql: execute using into
Previous:From: Pavel StehuleDate: 2006-03-27 19:25:33
Subject: proposal - plpgsql: execute using into

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group