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


> 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?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

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