Re: PL/pgSQL: dynamic tablename [resolved]

Tom Lane wrote:

> Jochem van Dieten <jochemd(at)oli(dot)tudelft(dot)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