Re: Weird EXECUTE ... USING behaviour

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