Thread: 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 //