Обсуждение: Passing the table name as a function argument in PLPGSQL

Hello,


Is it possible to pass the name of the table to update to a given function ?

For example, how can I do something like:

CREATE FUNCTION compute_ipaddr(CHARACTER VARYING, INET)
    AS $_$
DECLARE
   tbl_name ALIAS FOR $1;
   module_ipaddr ALIAS FOR $2;
BEGIN
 UPDATE tbl_name SET ipaddr = module_ipaddr  ....

END;
$_$
    LANGUAGE PLPGSQL;


Since this does not work, I have to do

IF tbl_name = 'controller' THEN
  UPDATE controller SET ipaddr = module_ipaddr ...
ELSE IF tbl_name = 'server' THEN
  UPDATE server SET ipaddr = module_ipaddr ...
[ ...]


Is there a technique to pass the table name and so make the code more
generic ?

Thanks for your help

Vincent

Vincent Ficet <jean-vincent.ficet@bull.net> wrote:

> Hello,
>
>
> Is it possible to pass the name of the table to update to a given function ?
>
> For example, how can I do something like:
>
> CREATE FUNCTION compute_ipaddr(CHARACTER VARYING, INET)
>     AS $_$
> DECLARE
>    tbl_name ALIAS FOR $1;
>    module_ipaddr ALIAS FOR $2;
> BEGIN
>  UPDATE tbl_name SET ipaddr = module_ipaddr  ....
>
> END;
> $_$
>     LANGUAGE PLPGSQL;
>
>
> Since this does not work, I have to do
>
> IF tbl_name = 'controller' THEN
>   UPDATE controller SET ipaddr = module_ipaddr ...
> ELSE IF tbl_name = 'server' THEN
>   UPDATE server SET ipaddr = module_ipaddr ...
> [ ...]
>
>
> Is there a technique to pass the table name and so make the code more
> generic ?

Sure.

Build your SQL as an TEXT-String and EXECUTE them.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°