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

2010/1/13 Vincenzo Romano <vincenzo.romano@notorand.it>:
> 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
>

Even worse!

This is one of my (best) attempts to work the issue around:

            execute $l2$
              select $l3$alter table $l2$||ct||$l2$ add check (
data>=$1::timestamp and data<$2::timestamp and maga=$3 )$l3$
            $l2$ into pr using rec.d0,rec.d1,rec.maga;
            raise info '%',pr;
            execute pr;

So, basically I (tried to) expand the ALTER TABLE command into a text
variable for later execution.
The RAISE statement is for basic debugging. The output is

INFO:  alter table public.test_part_1 add check ( data>=$1::timestamp
and data<$2::timestamp and maga=$3 )

despite the (usual) USING predicate!
Also in this case the $1, $2 and $3 "variables" have not been substituted.
Please, remember that 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;


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