Re: PL/pgSQL: dynamic tablename [resolved]

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