Re: Proposal: PL/pgSQL EXECUTE INTO USING (for 8.4)

2007/10/16, Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On 10/16/07, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > Hello,
> >
> > this proposal change older unaccepted proposal
> > http://archives.postgresql.org/pgsql-hackers/2006-03/msg01157.php .
> >
>
> > Compliance with PL/SQL
> > * You can use numeric, character, and string literals as bind arguments
> > * You cannot use bind arguments to pass the names of schema objects to
> > a dynamic SQL statement.
>
> does this mean you can't dynamically sub in a variable for a table
> name? if so, why keep that limitation? one of the main reasons to use
> dynamic sql is for schema objects.
>

No, it doesn't mean. You can create any SQL statement. Only you cannot
use binding (USING clause) for table name. Why? Because it's based on
prepared statements, and there you cannot use parameters for column's
or table's names.

You can: .. execute 'select * from || table || ' where a = $1' using var_a ..

Older patch was based on strings, and it was really ugly and without
any effects for security. Usually You have more params than table
names, so this limit is not too much hard. Now, patch is simple,
because there isn't any redundance.

Main reason for this patch is security. Not comfort for programmer.
But I belive, so it's good step forward.

Pavel

p.s. I though about it, and this is more consistent. You have only one
rule for params everywhere. ~ never use params as object names.