From: | "Phillip J(dot) Allen" <paallen(at)attglobal(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | How to quote in plpgsql function for Execute dynamic queries |
Date: | 2001-08-18 12:10:22 |
Message-ID: | 3B7E5B2E.1054CF9E@attglobal.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi all,
I am trying to write a simple plpgsql function that executes a dynamic
function and cannot get the quotes right even after reading the programmer
manual. I just can't seem to get my head around it. This is what I am trying
to do.
CREATE FUNCTION myfunc(float8) RETURNS float8 AS '
DECLARE
dpsql varchar;
dprec RECORD;
a float8;
f float8;
BEGIN
dpsql := 'Select d.a_parm, d.f_parm, d.deltax, d.deltay FROM c_g_datum
WHERE d.datum_id = ' || $1 || ';'; --this sql will only return 1 record
FOR dprec IN EXCECUTE dpsql LOOP
a := dprec.a_parm;
f := dprec.f_parm;
END LOOP;
-- DO SOME CALCUATIONS AND RETURN A FLOAT8 VALUE;
END;'
LANGUAGE 'plpgsql';
So the real question is how do I formate the dpsql string. I have returned
the string and executed an identical string in a querry and it works but for
some reason it bails out in an error when executed dynamically.
Does anyone have any fuctions that demonstrate how to properly quote
concatenated strings? Even after reading the manual I am confused. Thanks
Phillip J. Allen
Consulting Geochemist/Geologist
Lima Peru
e-mail: paallen(at)attglobal(dot)net
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-08-18 14:51:07 | Re: How to quote in plpgsql function for Execute dynamic queries |
Previous Message | Tom Robinson | 2001-08-17 23:43:03 | How to find size of a table |