Re: Passing the table name as a function argument in PLPGSQL

Vincent Ficet <jean-vincent(dot)ficet(at)bull(dot)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°