Date: Wed, 13 Jan 2010 19:10:53 GMT
Message-Id: <201001131910.o0DJArAo086208@wwwmaster.postgresql.org>
To: pgsql-bugs@postgresql.org
Subject: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
From: "Vincenzo Romano" <vincenzo.romano@notorand.it>
Content-Type: text/plain; charset=utf-8


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