Re: R: Re: Weird EXECUTE ... USING behaviour - Mailing list pgsql-general

2010/1/13 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 2010/1/13 Adrian Klaver <adrian.klaver@gmail.com>:
>> On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote:
>>> The static binding worked fine in the second EXECUTE USING statement but
>>> not in the first one.
>>> I still think that it's weird more than wishful.
>>> I can work it around, though.
>>>
>>> Il giorno 12 gen, 2010 4:13 p., "Tom Lane" <tgl@sss.pgh.pa.us> ha scritto:
>>>
>>> Vincenzo Romano <vincenzo.romano@notorand.it> writes:
>>> > I don't think so. Those variables should be evaluated with the USING >
>>>
>>> *before* the actual executi...
>>> Unfortunately, that's just wishful thinking, not how EXECUTE USING
>>> actually works.
>>>
>>>                        regards, tom lane
>>
>> Without the whole function it is hard to say. Given the error I would say it is
>> a quoting issue. The table name is being substituted for, the other parameters
>> are not. It acts like the add_check clause is not part of the EXECUTE statement
>> and is just being passed through verbatim.
>>
>> ERROR:  there is no parameter $1
>> CONTEXT: SQL statement "
>>              alter table public.test_part_2 add check(
>> data>=$1::timestamp and data<$2::timestamp and maga=$3 )
>
> Well, for these case I prefer $-quoting: it's my personal taste that should
> The rest of the function budy sheds no extra light on the problem.
> For sure this fragment works fine:
>
>           execute $l2$
>             insert into $l2$||ct||$l2$
>               select * from only public.test
>               where data>=$1::timestamp and data<$2::timestamp and maga=$3
>           $l2$ using rec.d0,rec.d1,rec.maga;
>
> while thos one doesn't:
>
>           execute $l2$
>             alter table $l2$||ct||$l2$ add check(
> data>=$1::timestamp and data<$2::timestamp and maga=$3 )
>           $l2$ using rec.d0,rec.d1,rec.maga;
>
> Please, observe that the WHERE condition and the USING predicate in
> the first fragment is exactly the same as
> the CHECK condition and the USING predicate in the second one (that's
> intentional).
> What I would still expect is that the EXECUTE ... USING statically
> replaces the $1,$2 and $3 "variables" in the quoted string with the
> *current values* of what can be found in the USING predicate.
> No function arguments should be even taken into account as the "thing"
> following the EXECUTE command is a *string literal*.
>
> In the end, I think that Tom is wrong, simply because one fragment
> works and the other one doesn't.
> I'd expect either both or none working and would say this is a bug.
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>

One can also check the documentation (v8.4.2) at page 800, chapter
"38.5.4. Executing Dynamic Commands"
<quote>
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:
EXECUTE ’SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2’
INTO c
USING checked_user, checked_date;
</quote>

Moreover, by putting the logging level to the maximum I've found where
the error is generated:

ERROR:  42P02: there is no parameter $1
...
LOCATION:  find_param_type, parse_expr.c:655

This is the backend (src/backend/parser), while I was expecting the
expansion to happen in the PL (src/pl/plpgsql/src).
This seems to me to confirm a bug where the actual string inside the
EXECUTE gets interpreted before (or without) the USING predicate,
at least in the case of the "ALTER TABLE", but not in the case of the SELECT.
Which in turn sounds even more weird to me.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS