Skip site navigation (1) Skip section navigation (2)

BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion

From: "Vincenzo Romano" <vincenzo(dot)romano(at)notorand(dot)it>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5274: [PL/PgSQL] EXECUTE ... USING variable expansion
Date: 2010-01-13 19:10:53
Message-ID: 201001131910.o0DJArAo086208@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5274
Logged by:          Vincenzo Romano
Email address:      vincenzo(dot)romano(at)notorand(dot)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

Responses

pgsql-bugs by date

Next:From: James BellingerDate: 2010-01-13 20:58:38
Subject: BUG #5275: validate_exec in port/exec.c only reads u/g/o, not ACLs
Previous:From: David E. WheelerDate: 2010-01-13 19:08:33
Subject: Termination When Switching between PL/Perl and PL/PerlU

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group