Hello it is not bug.
DDL statements like CREATE TABLE, ALTER TABLE are doesn't support parametrisation - you cannot use a placeholder for parameter everywhere. So you cannot to write PQexecParams(..."ALTER TABLE test ALTER COLUMN $1 ...", ...), so it cannot be supported by EXECUTE USING. Parameters are available only for DML statements - for statements with execution plan. You can store a plan and you can call stored plan with different parameters - it means - parameter cannot be a SQL identifier - like column or table name, because this changes a plan. so you can do EXECUTE 'SELECT * FROM tab WHERE col = $1' USING var1 but you cannot do: EXECUTE 'SELECT * FROM $1 WHERE col = 10' USING var1, because SELECT FROM tab1 or SELECT FROM tab2 needs different execution plans. You cannot do too: EXECUTE 'CREATE TABLE $1' USING var1, bacause CREATE TABLE is DDL statement without plan, and without possibility to use a parameters. You have to do: EXECUTE 'SELECT * FROM ' || var1::regclass || ' WHERE col=10' -- var1::regclass is minimum protection against SQL injection EXECUTE 'CREATE TABLE '|| quote_ident(var1) || '(.... Regards Pavel Stehule 2010/1/13 Vincenzo Romano <vincenzo.rom...@notorand.it>: > > The following bug has been logged online: > > Bug reference: 5274 > Logged by: Vincenzo Romano > Email address: vincenzo.rom...@notorand.it > PostgreSQL version: 8.4.2 > Operating system: Linux > Description: [PL/PgSQL] EXECUTE ... USING variable expansion > Details: > > My system says: > ~ lsb_release -a > LSB Version: > :core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:desktop-3.1-a > md64:desktop-3.1-noarch:desktop-3.2-amd64:desktop-3.2-noarch > Distributor ID: Fedora > Description: Fedora release 12 (Constantine) > Release: 12 > Codename: Constantine > > If you try the following: > > CREATE TABLE test ( i INT ); > > CREATE OR REPLACE FUNCTION func() > RETURNS void > LANGUAGE plpgsql > AS $function$ > DECLARE > e TEXT; > t TEXT; > i INT; > BEGIN > i := 42; > t := 'answer'; > EXECUTE 'SELECT $1' INTO e USING t; > RAISE INFO '%',e; > EXECUTE 'ALTER TABLE test ALTER COLUMN i SET DEFAULT $1' USING i; > END; > $function$; > > SELECT func(); > > The first EXECUTE...USING replaces the variable $1 with the value of the > variable "t". The first output line reads: > > INFO: answer > > The second EXECUTE...USING doesn't do the replacement and triggers an > error: > > ERROR: there is no parameter $1 > CONTEXT: SQL statement "ALTER TABLE test ALTER COLUMN i SET DEFAULT $1" > PL/pgSQL function "func" line 10 at EXECUTE statement > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
