Weird EXECUTE ... USING behaviour

Vincenzo Romano <vincenzo.romano@notorand.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@sss.pgh.pa.us>:

Vincenzo Romano <vincenzo.romano@notorand.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@sss.pgh.pa.us>:

Vincenzo Romano <vincenzo.romano@notorand.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.klaver@gmail.com

2010/1/12 Adrian Klaver <adrian.klaver@gmail.com>:

On Tuesday 12 January 2010 6:24:13 am Vincenzo Romano wrote:

2010/1/12 Tom Lane <tgl@sss.pgh.pa.us>:

Vincenzo Romano <vincenzo.romano@notorand.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.klaver@gmail.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.romano@notorand.it
fix. +39 0823 454163 | skype. notorand.it
fax. +39 02 700506964 | msn. notorand.it
NON QVIETIS MARIBVS NAVTA PERITVS

Vincenzo Romano <vincenzo.romano@notorand.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@sss.pgh.pa.us> ha scritto:

Vincenzo Romano <vincenzo.romano@notorand.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.romano@notorand.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@sss.pgh.pa.us> ha scritto:

Vincenzo Romano <vincenzo.romano@notorand.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@sss.pgh.pa.us> ha scritto:

Vincenzo Romano <vincenzo.romano@notorand.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.klaver@gmail.com

2010/1/13 Adrian Klaver <adrian.klaver@gmail.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@sss.pgh.pa.us> ha scritto:

Vincenzo Romano <vincenzo.romano@notorand.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.romano@notorand.it>:

2010/1/13 Adrian Klaver <adrian.klaver@gmail.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@sss.pgh.pa.us> ha scritto:

Vincenzo Romano <vincenzo.romano@notorand.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.romano@notorand.it>:

2010/1/13 Vincenzo Romano <vincenzo.romano@notorand.it>:

2010/1/13 Adrian Klaver <adrian.klaver@gmail.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@sss.pgh.pa.us> ha scritto:

Vincenzo Romano <vincenzo.romano@notorand.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.romano@notorand.it>:

2010/1/13 Vincenzo Romano<vincenzo.romano@notorand.it>:

2010/1/13 Adrian Klaver<adrian.klaver@gmail.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@sss.pgh.pa.us> ha scritto:

Vincenzo Romano<vincenzo.romano@notorand.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.klaver@gmail.com

My issue involves the USING predicates, though.

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

On 01/13/2010 09:37 AM, Vincenzo Romano wrote: > > 2010/1/13 Vincenzo
Romano<vincenzo.romano@notoran...
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.klaver@gmail.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.klaver@gmail.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.klaver@gmail.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.klaver@gmail.com

On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
vincenzo.romano@notorand.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.romano@notorand.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.klaver@gmail.com

2010/1/14 Adrian Klaver <adrian.klaver@gmail.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.romano@notorand.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.klaver@gmail.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.romano@notorand.it> wrote:

2010/1/14 Adrian Klaver <adrian.klaver@gmail.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.romano@notorand.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.klaver@gmail.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