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.
CREATE TABLE test ( i int );
EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING 42;
ERROR: there is no parameter $1
CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1"
EXECUTE 'SELECT $1' USING 42;
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 current documentation
doesn't say so and clearly describes how this feature is meant to
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:
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.
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS
pgsql-hackers by date
|Next:||From: Dimitri Fontaine||Date: 2010-01-14 14:47:02|
|Subject: Re: mailing list archiver chewing patches|
|Previous:||From: Magnus Hagander||Date: 2010-01-14 14:39:59|
|Subject: Re: Streaming replication, retrying from archive|