PL/pgSQL: dynamic tablename
I am building a set of functions to manage some trees. I would like to
use a dynamic tablename. Something like:
CREATE FUNCTION fn_test(varchar, integer, varchar, varchar) RETURNS
INTEGER AS '
DECLARE
beforenode INT4;
newid INT4;
BEGIN
SELECT INTO beforenode lft
FROM $4
WHERE ID = $2;
More processing
RETURN newid;
END;
' LANGUAGE 'plpgsql';
Problem is that I always get an error about $4 being used incorrectly.
If I hardcode the name of the table it works just fine.
Does anybody have any suggestion on how to use a dynamic tablename
passed as an attribute in a function?
Jochem
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
Does anybody have any suggestion on how to use a dynamic tablename
passed as an attribute in a function?
You need to use EXECUTE. See past discussions.
regards, tom lane
Jochem,
You will need to use EXECUTE [sql code] from within your function if your
using dynamic sql. For reasons why + examples please see:
http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQ
L-STATEMENTS-EXECUTING-DYN-QUERIES
hih
steve boyle
----- Original Message -----
From: "Jochem van Dieten" <jochemd@oli.tudelft.nl>
To: <pgsql-general@postgresql.org>
Sent: Sunday, January 20, 2002 9:08 PM
Subject: [GENERAL] PL/pgSQL: dynamic tablename
Show quoted text
I am building a set of functions to manage some trees. I would like to
use a dynamic tablename. Something like:CREATE FUNCTION fn_test(varchar, integer, varchar, varchar) RETURNS
INTEGER AS '
DECLARE
beforenode INT4;
newid INT4;
BEGIN
SELECT INTO beforenode lft
FROM $4
WHERE ID = $2;More processing
RETURN newid;
END;
' LANGUAGE 'plpgsql';Problem is that I always get an error about $4 being used incorrectly.
If I hardcode the name of the table it works just fine.Does anybody have any suggestion on how to use a dynamic tablename
passed as an attribute in a function?Jochem
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Tom Lane wrote:
Jochem van Dieten <jochemd@oli.tudelft.nl> writes:
Does anybody have any suggestion on how to use a dynamic tablename
passed as an attribute in a function?You need to use EXECUTE. See past discussions.
For the record:
A nested FOR ... IN EXECUTE got me there.
CREATE FUNCTION fn_test(varchar, varchar, integer, varchar) RETURNS
INTEGER AS '
DECLARE
a_output VARCHAR(4000);
b_output VARCHAR(4000);
c_output VARCHAR(4000);
d_output VARCHAR(4000);
e_output VARCHAR(4000);
oldfield VARCHAR(10);
oldinfo RECORD;
newinfo RECORD;
BEGIN
IF $2 = ''ADD'' THEN
IF $4 = ''sibling'' THEN
oldfield := ''lft'';
ELSE
oldfield := ''rgt'';
END IF;
a_output = ''SELECT '' || oldfield || '' AS beforeValue FROM '' ||
$1 || '' WHERE ID = '' || $3;
FOR oldinfo IN EXECUTE a_output LOOP
b_output = ''UPDATE '' || $1 || '' SET rgt = rgt + 2 WHERE rgt >= '' ||
oldinfo.beforeValue;
c_output = ''UPDATE '' || $1 || '' SET lft = lft + 2 WHERE lft >= '' ||
oldinfo.beforeValue;
d_output = ''INSERT INTO '' || $1 || '' (lft, rgt) VALUES ('' ||
oldinfo.beforeValue || '' - 2, '' || oldinfo.beforeValue || '' - 1)'';
e_output = ''SELECT currval('''''' || $1 || ''_id_seq'''') AS
newid'';
EXECUTE b_output;
EXECUTE c_output;
EXECUTE d_output;
FOR newinfo IN EXECUTE e_output LOOP
RETURN newinfo.newid;
END LOOP;
END LOOP;
RETURN 67;
END IF;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
Thanks,
Jochem