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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
