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

Hello

I thing, so this is bad idea.

a) this behave depends on DDL implementation, not plpgsql implementation

b) proposed implementation needs some escape magic. This was first
implementation of USING clause and it was rejected. Some composite and
nested values are significant break.

see in archive http://archives.postgresql.org/pgsql-patches/2006-08/msg00267.php

Regards
Pavel Stehule

2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>