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