Weird EXECUTE ... USING behaviour

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
CONTEXT: SQL statement "
alter table public.test_part_2 add check(
data>=$1::timestamp and data<$2::timestamp and maga=$3 )
"
PL/pgSQL function "f_partition_test" line 25 at istruzione EXECUTE
----
whiile this fragment:
----
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;
----
is executed without a glitch in the very same function body.
Where's my error?

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

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

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

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

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

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 execution.
> Thus my statements only contain columns and constants.

Unfortunately, that's just wishful thinking, not how EXECUTE USING
actually works.

regards, tom lane

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

2010/1/13 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 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
>

Sorry for top posting: I used a mobile phone ...

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

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 )

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

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

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

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;

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

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

My issue involves the USING predicates, though.

Il giorno 13 gen, 2010 10:26 p., "Adrian Klaver" <adrian(dot)klaver(at)gmail(dot)com>
ha scritto:

On 01/13/2010 09:37 AM, Vincenzo Romano wrote: > > 2010/1/13 Vincenzo
Romano<vincenzo(dot)romano(at)notoran(dot)(dot)(dot)
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

On 01/13/2010 01:39 PM, Vincenzo Romano wrote:
> My issue involves the USING predicates, though.
>

WARNING:Old joke

Doctor: What is wrong?
Patient: My elbow hurts when I do this, what should I do?
Doctor: Quit doing that.

USING is not working the way you want, mainly for the reason you found,
the CHECK is being parsed before the variable is substituted. At this
point it is time to do something different.

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

It is not the check or the select.
It is the way the substitution has been implemented. It looks like the code
replaces the variable name and not the value.
Which is different from what is written at page 800.
I only hope they won't change the manual to match the feature/bug (warning:
new joke)

Il giorno 13 gen, 2010 10:53 p., "Adrian Klaver" <adrian(dot)klaver(at)gmail(dot)com>
ha scritto:

On 01/13/2010 01:39 PM, Vincenzo Romano wrote: > > My issue involves the
USING predicates, though. >...
WARNING:Old joke

Doctor: What is wrong?
Patient: My elbow hurts when I do this, what should I do?
Doctor: Quit doing that.

USING is not working the way you want, mainly for the reason you found, the
CHECK is being parsed before the variable is substituted. At this point it
is time to do something different.

--

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

On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
vincenzo(dot)romano(at)notorand(dot)it> wrote:

> It is not the check or the select.
> It is the way the substitution has been implemented. It looks like the code
> replaces the variable name and not the value.
> Which is different from what is written at page 800.
> I only hope they won't change the manual to match the feature/bug (warning:
> new joke)
>

Page 800:

"Another restriction on parameter symbols is that they only work in SELECT,
INSERT, UPDATE, and DELETE commands. In other statement types (generically
called utility statements), you must insert values textually even if they
are just data values."

On Wednesday 13 January 2010 2:17:51 pm Scott Mead wrote:
> On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
>
> vincenzo(dot)romano(at)notorand(dot)it> wrote:
> > It is not the check or the select.
> > It is the way the substitution has been implemented. It looks like the
> > code replaces the variable name and not the value.
> > Which is different from what is written at page 800.
> > I only hope they won't change the manual to match the feature/bug
> > (warning: new joke)
>
> Page 800:
>
> "Another restriction on parameter symbols is that they only work in SELECT,
> INSERT, UPDATE, and DELETE commands. In other statement types (generically
> called utility statements), you must insert values textually even if they
> are just data values."

Scott, thanks for that I must have read through that section several times at
least with out picking up on it.

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

2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
> On Wednesday 13 January 2010 2:17:51 pm Scott Mead wrote:
>> On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
>>
>> vincenzo(dot)romano(at)notorand(dot)it> wrote:
>> > It is not the check or the select.
>> > It is the way the substitution has been implemented. It looks like the
>> > code replaces the variable name and not the value.
>> > Which is different from what is written at page 800.
>> > I only hope they won't change the manual to match the feature/bug
>> > (warning: new joke)
>>
>> Page 800:
>>
>> "Another restriction on parameter symbols is that they only work in SELECT,
>> INSERT, UPDATE, and DELETE commands. In other statement types (generically
>> called utility statements), you must insert values textually even if they
>> are just data values."
>
> Scott, thanks for that I must have read through that section several times at
> least with out picking up on it.
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

Really?

That section is not in any page of the v8.4.2 documentation either PDF or HTML.
The sentence has been introduced (yesterday?) in 8.5devel, which is
far from being "current".

I only hope they won't change the manual to match the feature/bug
(warning: new joke)

So that was not a joke at all! :-(

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

On Thu, Jan 14, 2010 at 7:19 AM, Vincenzo Romano <
vincenzo(dot)romano(at)notorand(dot)it> wrote:

> 2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
> > On Wednesday 13 January 2010 2:17:51 pm Scott Mead wrote:
> >> On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
> >>
> >> vincenzo(dot)romano(at)notorand(dot)it> wrote:
> >> > It is not the check or the select.
> >> > It is the way the substitution has been implemented. It looks like the
> >> > code replaces the variable name and not the value.
> >> > Which is different from what is written at page 800.
> >> > I only hope they won't change the manual to match the feature/bug
> >> > (warning: new joke)
> >>
> >> Page 800:
> >>
> >> "Another restriction on parameter symbols is that they only work in
> SELECT,
> >> INSERT, UPDATE, and DELETE commands. In other statement types
> (generically
> >> called utility statements), you must insert values textually even if
> they
> >> are just data values."
> >
> > Scott, thanks for that I must have read through that section several
> times at
> > least with out picking up on it.
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)gmail(dot)com
> >
>
> Really?
>
> That section is not in any page of the v8.4.2 documentation either PDF or
> HTML.
> The sentence has been introduced (yesterday?) in 8.5devel, which is
> far from being "current".
>
> I only hope they won't change the manual to match the feature/bug
> (warning: new joke)
>
> So that was not a joke at all! :-(
>

Well it is in 8.5 Devel, so it could have been added immediately after
your thread started yesterday, I'm honestly not sure.

http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html

Sorry for not posting that. Either way, I if you really want a feature like
this added, it's usually better to take people's word and then make the case
for adding the feature. If you declare it a bug and get belligerent, it
makes it harder to get features you'd like added. I would say that coming
into this asking for a new feature would maybe have helped gain more ground.

Good luck

--Scott

PS -- I did see this in the 8.3 Docs after writing this note:

http://www.postgresql.org/docs/8.3/static/plpgsql-implementation.html

and I do see it here:

http://www.postgresql.org/docs/current/static/plpgsql-implementation.html

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

Scott Mead <scott(dot)lists(at)enterprisedb(dot)com> writes:
> Well it is in 8.5 Devel, so it could have been added immediately after
> your thread started yesterday, I'm honestly not sure.

The particular paragraph mentioned was committed here
http://archives.postgresql.org/pgsql-committers/2009-11/msg00094.php
but as you note it was just a relocation of a comment that appeared
elsewhere for at least two years before that (and even then, it was
just documenting behavior that had existed since day one).

regards, tom lane

The documentation says (also in v8.5) "These symbols refer to values
supplied in the USING clause".
"values"and not "variable name or reference". This leads to the useful
feature mentioned a line later in the same page.
Once you have a value replaced you can avoid the restrictions you now
mention on v8.5.

What's the right place to submit proposals?

Il giorno 14 gen, 2010 7:42 m., "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> ha scritto:

Scott Mead <scott(dot)lists(at)enterprisedb(dot)com> writes: > Well it is in 8.5 Devel,
so it could have bee...
The particular paragraph mentioned was committed here
http://archives.postgresql.org/pgsql-committers/2009-11/msg00094.php
but as you note it was just a relocation of a comment that appeared
elsewhere for at least two years before that (and even then, it was
just documenting behavior that had existed since day one).

regards, tom lane

2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> The documentation says (also in v8.5) "These symbols refer to values
> supplied in the USING clause".
> "values"and not "variable name or reference". This leads to the useful
> feature mentioned a line later in the same page.
> Once you have a value replaced you can avoid the restrictions you now
> mention on v8.5.
>
> What's the right place to submit proposals?

it's conference pg_hackers.

Pavel Stehule

>
> Il giorno 14 gen, 2010 7:42 m., "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> ha scritto:
>
> Scott Mead <scott(dot)lists(at)enterprisedb(dot)com> writes: > Well it is in 8.5 Devel,
> so it could have bee...
>
> The particular paragraph mentioned was committed here
> http://archives.postgresql.org/pgsql-committers/2009-11/msg00094.php
> but as you note it was just a relocation of a comment that appeared
> elsewhere for at least two years before that (and even then, it was
> just documenting behavior that had existed since day one).
>
>                        regards, tom lane
>

On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote:

> >
> > Scott, thanks for that I must have read through that section several
> > times at least with out picking up on it.
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)gmail(dot)com
>
> Really?
>
> That section is not in any page of the v8.4.2 documentation either PDF or
> HTML. The sentence has been introduced (yesterday?) in 8.5devel, which is
> far from being "current".

http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html
38.10.1. Variable Substitution
Last paragraph.

>
> I only hope they won't change the manual to match the feature/bug
> (warning: new joke)
>
> So that was not a joke at all! :-(

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

2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote:
>
>> >
>> > Scott, thanks for that I must have read through that section several
>> > times at least with out picking up on it.
>> >
>> > --
>> > Adrian Klaver
>> > adrian(dot)klaver(at)gmail(dot)com
>>
>> Really?
>>
>> That section is not in any page of the v8.4.2 documentation either PDF or
>> HTML. The sentence has been introduced (yesterday?) in 8.5devel, which is
>> far from being "current".
>
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html
> 38.10.1. Variable Substitution
> Last paragraph.
>
>>
>>     I only hope they won't change the manual to match the feature/bug
>> (warning: new joke)
>>
>> So that was not a joke at all! :-(
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

Now I'm pretty surfe it's a bug.

CREATE OR REPLACE FUNCTION f()
RETURNS VOID
LANGUAGE plpgsql
AS $function$
DECLARE
cmd TEXT;
BEGIN
EXECUTE '
SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$
' INTO cmd USING 42;
RAISE INFO '%',cmd;
END;
$function$

SELECT f();
INFO: ALTER TABLE test ALTER COLUMN i SET DEFAULT $1

The command to be executed is DML (SELECT). The substitution doesn't take place.

--
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

2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
>> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote:
>>
>>> >
>>> > Scott, thanks for that I must have read through that section several
>>> > times at least with out picking up on it.
>>> >
>>> > --
>>> > Adrian Klaver
>>> > adrian(dot)klaver(at)gmail(dot)com
>>>
>>> Really?
>>>
>>> That section is not in any page of the v8.4.2 documentation either PDF or
>>> HTML. The sentence has been introduced (yesterday?) in 8.5devel, which is
>>> far from being "current".
>>
>> http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html
>> 38.10.1. Variable Substitution
>> Last paragraph.
>>
>>>
>>>     I only hope they won't change the manual to match the feature/bug
>>> (warning: new joke)
>>>
>>> So that was not a joke at all! :-(
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)gmail(dot)com
>>
>
> Now I'm pretty surfe it's a bug.
>
>
> CREATE OR REPLACE FUNCTION f()
> RETURNS VOID
> LANGUAGE plpgsql
> AS $function$
> DECLARE
>  cmd TEXT;
> BEGIN
>  EXECUTE '
>    SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$
>  ' INTO cmd USING 42;
>  RAISE INFO '%',cmd;
> END;
> $function$
>
> SELECT f();
> INFO:  ALTER TABLE test ALTER COLUMN i SET DEFAULT $1
>
> The command to be executed is DML (SELECT). The substitution doesn't take place.

yes. You cannot call SELECT 'ALTER ...'

Regards
Pavel Stehule

>
> --
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

2010/1/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>> 2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
>>> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote:
...
>> CREATE OR REPLACE FUNCTION f()
>> RETURNS VOID
>> LANGUAGE plpgsql
>> AS $function$
>> DECLARE
>>  cmd TEXT;
>> BEGIN
>>  EXECUTE '
>>    SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$
>>  ' INTO cmd USING 42;
>>  RAISE INFO '%',cmd;
>> END;
>> $function$
>>
>> SELECT f();
>> INFO:  ALTER TABLE test ALTER COLUMN i SET DEFAULT $1
>>
>> The command to be executed is DML (SELECT). The substitution doesn't take place.
>
> yes. You cannot call SELECT 'ALTER ...'

SELECT 'ALTER ...' is to select a text string into a variable!
You mean the parse will give a look into my constant string to see
whether I'm trying to build a dynamic DDL command?
This would be awesome!

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

2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2010/1/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> 2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>>> 2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
>>>> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote:
> ...
>>> CREATE OR REPLACE FUNCTION f()
>>> RETURNS VOID
>>> LANGUAGE plpgsql
>>> AS $function$
>>> DECLARE
>>>  cmd TEXT;
>>> BEGIN
>>>  EXECUTE '
>>>    SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$
>>>  ' INTO cmd USING 42;
>>>  RAISE INFO '%',cmd;
>>> END;
>>> $function$
>>>
>>> SELECT f();
>>> INFO:  ALTER TABLE test ALTER COLUMN i SET DEFAULT $1
>>>
>>> The command to be executed is DML (SELECT). The substitution doesn't take place.
>>
>> yes. You cannot call SELECT 'ALTER ...'
>
> SELECT 'ALTER ...' is to select a text string into a variable!
> You mean the parse will give a look into my constant string to see
> whether I'm trying to build a dynamic DDL command?
> This would be awesome!
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>

This instead works:

CREATE OR REPLACE FUNCTION public.f()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
cmd1 TEXT;
cmd2 TEXT;
cmd3 TEXT;
BEGIN
cmd1 := 'ALTER TABLE test ALTER COLUMN i SET DEFAULT ';
EXECUTE 'SELECT $1' INTO cmd2 USING 42;
cmd3 := cmd1||cmd2;
RAISE INFO '%',cmd3;
execute cmd3;
END;
$function$

The point (in my case) is that the list of expressions (not variables)
after the USING is dynamic itself.
I can also put 42 into a variable and use it's value after the USING.
But this is a lot of extra work just because the values after the
USING lexeme are not evaluated by the plpgsql
and replaced. It will be the SQL engine itself.

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

2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2010/1/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> 2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>>> 2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
>>>> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote:
> ...
>>> CREATE OR REPLACE FUNCTION f()
>>> RETURNS VOID
>>> LANGUAGE plpgsql
>>> AS $function$
>>> DECLARE
>>>  cmd TEXT;
>>> BEGIN
>>>  EXECUTE '
>>>    SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$
>>>  ' INTO cmd USING 42;
>>>  RAISE INFO '%',cmd;
>>> END;
>>> $function$
>>>
>>> SELECT f();
>>> INFO:  ALTER TABLE test ALTER COLUMN i SET DEFAULT $1
>>>
>>> The command to be executed is DML (SELECT). The substitution doesn't take place.
>>
>> yes. You cannot call SELECT 'ALTER ...'
>
> SELECT 'ALTER ...' is to select a text string into a variable!
> You mean the parse will give a look into my constant string to see
> whether I'm trying to build a dynamic DDL command?
> This would be awesome!
>

sorry. This is too much complicate.

Why do you use SELECT?

just

EXECUTE 'ALTER ... SET DEFAULT ' || 42.

There is other argument against USING + DDL. ALTER clause has syntax:

ALTER TABLE x SET DEFAULT expr.

but with USING clause you can pass only a value

Pavel

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

2010/1/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>> 2010/1/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>> 2010/1/14 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>>>> 2010/1/14 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>:
>>>>> On Wednesday 13 January 2010 10:19:57 pm Vincenzo Romano wrote:
>> ...
>>>> CREATE OR REPLACE FUNCTION f()
>>>> RETURNS VOID
>>>> LANGUAGE plpgsql
>>>> AS $function$
>>>> DECLARE
>>>>  cmd TEXT;
>>>> BEGIN
>>>>  EXECUTE '
>>>>    SELECT $l0$ALTER TABLE test ALTER COLUMN i SET DEFAULT $1 $l0$
>>>>  ' INTO cmd USING 42;
>>>>  RAISE INFO '%',cmd;
>>>> END;
>>>> $function$
>>>>
>>>> SELECT f();
>>>> INFO:  ALTER TABLE test ALTER COLUMN i SET DEFAULT $1
>>>>
>>>> The command to be executed is DML (SELECT). The substitution doesn't take place.
>>>
>>> yes. You cannot call SELECT 'ALTER ...'
>>
>> SELECT 'ALTER ...' is to select a text string into a variable!
>> You mean the parse will give a look into my constant string to see
>> whether I'm trying to build a dynamic DDL command?
>> This would be awesome!
>>
>
> sorry. This is too much complicate.
>
> Why do you use SELECT?
>
> just
>
> EXECUTE 'ALTER ... SET DEFAULT ' || 42.
>
> There is other argument against USING + DDL. ALTER clause has syntax:
>
> ALTER TABLE x SET DEFAULT expr.
>
> but with USING clause you can pass only a value
>
> Pavel
>
>> --
>> Vincenzo Romano
>> NotOrAnd Information Technologies
>> NON QVIETIS MARIBVS NAVTA PERITVS
>>

This is a fairly complete background of my issue.
At the very base I'm talking about a two level dynamic SQL (I have a
function which creates functions that compose and run the dynamic
SQL), thus the usage of $l0$ (higher level) and $l2$ (lower level)
quoting.
In particular, mt (master table), "co" (constraint) and "va" (variable
arguments) are text strings at the higher level and will become SQL in
the lower level. Those data are retrieved from configuration tables
and are no way static.
While "ct" (child table) is unknown at the higher level but is defined
at the lower level as a text string to become SQL at the
EXECUTE...USING level.

1. I need to create at run-time a number of child tables
(http://www.postgresql.org/docs/8.4/static/ddl-inherit.html).
I can easily do this with something like (there's no need for the USING clause):

execute $l2$
create table $l2$||ct||$l2$ (
like $l0$||mt||$l0$
including defaults
including constraints
including indexes
)
$l2$;

2. Then I need to fill those tables up from the master:
execute $l2$
insert into $l2$||ct||$l2$
select * from only $l0$||mt||$l0$
where $l0$||co||$l0$
$l2$ using $l0$||va||$l0$;

here "co" (constraint) and "va" (variable arguments) are text
variables taken from configuration tables.
What I have is something like:
co := 'recorddate>=$1 and recorddate<$2 and afield=$3'
va := 'rec.d0,rec.d1,rec.afield'
This also works as it is DML and I expect (and can actually see) the
$1,$2 and $3 *values* replaced into the string *before* it is sent to
the execution.

3. Then I need to add the TABLE-level CHECK condition in order to
exploit the "constraint_exclusion = on":
execute $l2$
alter table $l2$||ct||$l2$
add check( $l0$||co||$l0$ )
$l2$ using $l0$||va||$l0$;

This doesn't work for a number of reasons.

As you can see, there no easy way to replace the actual values taken
accordingly to "va" into the template "co" but using the USING clause.
This is the real and central knot in my problem.

So I tried to implement step #3 into a two pass process, one to just
expand the placeholders, one to execute a completely static DDL
command. Something like:

execute $l2$
select '$l0$||co||$l0$'
$l2$ into textvar using $l0$||va||$l0$;

execute $l2$
alter table $l2$||ct||$l2$
add check( $l2$||textvar||$l2$ )
$l2$;
This also doesn't work as:
execute $l2$select '$1'$l2$ into textvar using 'hello';
will not expand the $1 placeholder probably because it is within quotes.
If I skip the quotes, the select won't be able to find variables
mentioned into "co".

So, this is why I used SELECT. Unless there's a better (and working) advise.

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

>
> 3. Then I need to add the TABLE-level CHECK condition in order to
> exploit the "constraint_exclusion = on":
>            execute $l2$
>              alter table $l2$||ct||$l2$
>                add check(  $l0$||co||$l0$ )
>            $l2$ using $l0$||va||$l0$;
>

I am sorry, I am out. Your code isn't much readable:

Minimally there isn't placeholder - some like $x

It is game for the most cryptografic code :).

You searching a functionality that isn't in pg now :(. Clause USING
doesn't work like macros or templates in C++

look on this page - maybe it could be useful for you
http://wiki.postgresql.org/wiki/Sprintf

Regards
Pavel

2010/1/15 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>
>> 3. Then I need to add the TABLE-level CHECK condition in order to
>> exploit the "constraint_exclusion = on":
>>            execute $l2$
>>              alter table $l2$||ct||$l2$
>>                add check(  $l0$||co||$l0$ )
>>            $l2$ using $l0$||va||$l0$;
>>
>
> I am sorry, I am out. Your code isn't much readable:
>
> Minimally there isn't placeholder - some like $x
>
> It is game for the most cryptografic code :).
>
> You searching a functionality that isn't in pg now :(. Clause USING
> doesn't work like macros or templates in C++
>
> look on this page - maybe it could be useful for you
> http://wiki.postgresql.org/wiki/Sprintf
>
> Regards
> Pavel
>

Pavel,
It's worse than cryptographic if you skip the starting points. I do
admit it's far from plain PLPGSQL, though.
The "co" text variable contains the $x placeholders, the "va" text
variable contains the expressions to be used.

--
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

2010/1/15 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> look on this page - maybe it could be useful for you
> http://wiki.postgresql.org/wiki/Sprintf

This one could save my day!
Thanks Pavel.

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

2010/1/15 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2010/1/15 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>
>>> 3. Then I need to add the TABLE-level CHECK condition in order to
>>> exploit the "constraint_exclusion = on":
>>>            execute $l2$
>>>              alter table $l2$||ct||$l2$
>>>                add check(  $l0$||co||$l0$ )
>>>            $l2$ using $l0$||va||$l0$;
>>>
>>
>> I am sorry, I am out. Your code isn't much readable:
>>
>> Minimally there isn't placeholder - some like $x
>>
>> It is game for the most cryptografic code :).
>>
>> You searching a functionality that isn't in pg now :(. Clause USING
>> doesn't work like macros or templates in C++
>>
>> look on this page - maybe it could be useful for you
>> http://wiki.postgresql.org/wiki/Sprintf
>>
>> Regards
>> Pavel
>>
>
> Pavel,
> It's worse than cryptographic if you skip the starting points. I do
> admit it's far from plain PLPGSQL, though.
> The "co" text variable contains the $x placeholders, the "va" text
> variable contains the expressions to be used.

this is bad. Placeholders are used only when are explicit - tj they
are in string constant. Content of variables are protected.

Pavel

>
> --
> 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
>

2010/1/15 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2010/1/15 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
>> 2010/1/15 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>>
>>>> 3. Then I need to add the TABLE-level CHECK condition in order to
>>>> exploit the "constraint_exclusion = on":
>>>>            execute $l2$
>>>>              alter table $l2$||ct||$l2$
>>>>                add check(  $l0$||co||$l0$ )
>>>>            $l2$ using $l0$||va||$l0$;
>>>>
>>>
>>> I am sorry, I am out. Your code isn't much readable:
>>>
>>> Minimally there isn't placeholder - some like $x
>>>
>>> It is game for the most cryptografic code :).
>>>
>>> You searching a functionality that isn't in pg now :(. Clause USING
>>> doesn't work like macros or templates in C++
>>>
>>> look on this page - maybe it could be useful for you
>>> http://wiki.postgresql.org/wiki/Sprintf
>>>
>>> Regards
>>> Pavel
>>>
>>
>> Pavel,
>> It's worse than cryptographic if you skip the starting points. I do
>> admit it's far from plain PLPGSQL, though.
>> The "co" text variable contains the $x placeholders, the "va" text
>> variable contains the expressions to be used.
>
> this is bad. Placeholders are used only when are explicit - tj they
> are in string constant. Content of variables are protected.

sorry - it isn't exactly true. depend on nesting level.

Pavel

>
> Pavel
>
>>
>> --
>> 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
>>
>

2010/1/15 Vincenzo Romano <vincenzo(dot)romano(at)notorand(dot)it>:
> 2010/1/15 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> look on this page - maybe it could be useful for you
>> http://wiki.postgresql.org/wiki/Sprintf
>
> This one could save my day!
> Thanks Pavel.

EXECUTE PRINTF( .... );

It's great.
It's working.
It's a must-have-as-builtin!

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