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

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