Re: R: Re: Weird EXECUTE ... USING behaviour

On 01/13/2010 09:37 AM, Vincenzo Romano wrote:
> 2010/1/13 Vincenzo Romano<vincenzo(dot)romano(at)notorand(dot)it>:
>> 2010/1/13 Vincenzo Romano<vincenzo(dot)romano(at)notorand(dot)it>:
>>> 2010/1/13 Adrian Klaver<adrian(dot)klaver(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> ha scritto:
>>>>>
>>>>> Vincenzo Romano<vincenzo(dot)romano(at)notorand(dot)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;
>
>

CREATE OR REPLACE FUNCTION public.alter_test(tbl text)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
len integer :=3;
BEGIN
RAISE NOTICE '%,%' ,len,$1;
EXECUTE '
alter table '||tbl||' add check(length(tc_table_code) <
'||len||' )';
RETURN;
END;
$function$

Some playing around got the above to work for a test case on my machine
(8.4). The substitution is done before the check is parsed.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com