dynamic SQL - variable substitution in plpgsql
Hi all,
i could not do variable substitution in plpgsql procedure.
The variable names are taken as it is but not substituted in the SQL query.
what could be the problem ?
code looks like this:
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION test(a text) RETURNS SETOF RECORD AS $$
DECLARE
a text;
b text;
BEGIN
IF a = 'odd' THEN
b := 10;
c := 30;
ELIF a = 'even' THEN
b := 20;
c := 40;
END IF;
FOR result IN "SELECT x,y,z FROM mydata WHERE x = a AND y < b AND z > c" LOOP
RETURN NEXT result;
END LOOP;
END;
$$ language 'pgplsql';
-------------------------------------------------------------
tia
KM
km wrote:
Hi all,
i could not do variable substitution in plpgsql procedure.
The variable names are taken as it is but not substituted in the SQL query.
what could be the problem ?FOR result IN "SELECT x,y,z FROM mydata WHERE x = a AND y < b AND z > c" LOOP
RETURN NEXT result;
I don't think you need the quotes around your select statement.
Later,
--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration
km wrote:
Hi all,
i could not do variable substitution in plpgsql procedure.
The variable names are taken as it is but not substituted in the SQL query.
what could be the problem ?
Does this example even compile? I doubt that...
code looks like this:
------------------------------------------------------------------
CREATE OR REPLACE FUNCTION test(a text) RETURNS SETOF RECORD AS $$
DECLARE
a text;
b text;
I think you meant:
DECLARE
b text;
c text;
You redeclared a and never declared c.
BEGIN
IF a = 'odd' THEN
b := 10;
c := 30;
ELIF a = 'even' THEN
b := 20;
c := 40;
END IF;
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //