On 01/13/2010 09:37 AM, Vincenzo Romano wrote:
2010/1/13 Vincenzo Romano<vincenzo.romano@xxxxxxxxxxx>:
2010/1/13 Vincenzo Romano<vincenzo.romano@xxxxxxxxxxx>:
2010/1/13 Adrian Klaver<adrian.klaver@xxxxxxxxx>:
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@xxxxxxxxxxxxx> ha scritto:
Vincenzo Romano<vincenzo.romano@xxxxxxxxxxx> 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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
