From: Vincenzo Romano <vincenzo.romano@notorand.it>
To: pgsql-bugs@postgresql.org
Subject: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
Date: Wed, 13 Jan 2010 19:10:53 GMT
Message-ID: <201001131910.o0DJArAo086208@wwwmaster.postgresql.org> (raw)
The following bug has been logged online:
Bug reference: 5274
Logged by: Vincenzo Romano
Email address: vincenzo.romano@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: vincenzo.romano@notorand.it
Subject: Re: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
In-Reply-To: <201001131910.o0DJArAo086208@wwwmaster.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox