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

[PL/PgSQL] EXECUTE...USING enhancement proposal

From: Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [PL/PgSQL] EXECUTE...USING enhancement proposal
Date: 2010-01-14 14:44:20
Message-ID: 3eff28921001140644n24273631i7e3f0f016255c861@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi all.
There's currently a limitation in the v8.4.2 implementation of the
EXECUTE...USING predicate in PL/PgSQL which prevents you from
exploiting the USING-supplied value list with DDL commands.
For example:

CREATE TABLE test ( i int );
...
EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;

complains with:

ERROR:  there is no parameter $1
CONTEXT:  SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"

while:

EXECUTE 'SELECT $1' USING 42;

works.
In both cases the $1 variable/placeholder refers to a constant value.
And actually, even if the "thing" defined after the USING lexeme was a
variable, that should be evaluated and substituted *before* executing
the command.

The current documentation
(http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN)
doesn't say so and clearly describes how this feature is meant to
work.
Quoting:
----
The command string can use parameter values, which are referenced in
the command as $1, $2,
etc. These symbols refer to values supplied in the USING clause. This
method is often preferable to
inserting data values into the command string as text: it avoids
run-time overhead of converting the
values to text and back, and it is much less prone to SQL-injection
attacks since there is no need for
quoting or escaping. An example is:
----
(38.5.4. Executing Dynamic Commands)

It talks about "values", that is typed constants.
Please, refer also to the following discussion on pgsql-general mailing list:
http://archives.postgresql.org/pgsql-general/2010-01/msg00522.php

My proposal is to relax that (clearly running but undocumented)
constraint and allow any SQL command in the EXECUTE...USING predicate.
I would leave the responsibility to the programmer to ensure whether
the dynamic command makes any syntactic and semantic sense.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

Responses

pgsql-hackers by date

Next:From: Dimitri FontaineDate: 2010-01-14 14:47:02
Subject: Re: mailing list archiver chewing patches
Previous:From: Magnus HaganderDate: 2010-01-14 14:39:59
Subject: Re: Streaming replication, retrying from archive

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