Re: Weird EXECUTE ... USING behaviour

2010/1/12 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
> On Tuesday 12 January 2010 6:24:13 am Vincenzo Romano wrote:
>> 2010/1/12 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> > Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it> writes:
>> >> In a PL/PgSQL function I have the following:
>> >> ----
>> >>             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;
>> >> ----
>> >> which yields to this error messsge:
>> >> ERROR:  there is no parameter $1
>> >
>> > You can't use a parameter of the function in a CHECK constraint on a
>> > table.  The CHECK constraint is permanent and can't refer to transient
>> > state like that.
>> >
>> >                        regards, tom lane
>>
>> Tom, $1, $2 and $3 should be the substitution arguments from the USING
>> predicate, not the function argument list, which in my case is an
>> empty list!
>> And the EXECUTE shoud implement a static binding with the "variables"
>> from the USING predicate ...
>>
>> --
>> Vincenzo Romano
>> NotOrAnd Information Technologies
>> NON QVIETIS MARIBVS NAVTA PERITVS
>
> Its hard to tell from the above, but I believe you are having problems with
> this:
>
> "Currently, CHECK expressions cannot contain subqueries nor refer to variables
> other than columns of the current row. "
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

I don't think so. Those variables should be evaluated with the USING
*before* the actual execution.
Thus my statements only contain columns and constants.

--
Vincenzo Romano
NotOrAnd Information Technologies
cel. +39 339 8083886 | gtalk. vincenzo(dot)romano(at)notorand(dot)it
fix. +39 0823 454163 | skype. notorand.it
fax. +39 02 700506964 | msn. notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS