postgresql pass table reference to the function

Question

How can I pass table reference to the function? I tried following code but I'm getting errors:

CREATE OR REPLACE FUNCTION merge_(text,n VARCHAR(32),s VARCHAR(32),val int) RETURNS VOID AS 
$$
  DECLARE
    _table ALIAS FOR $1;
  BEGIN
    RAISE NOTICE '_table = %', _table;
...


SELECT merge_('testtable','h','a',50000);

NOTICE: _table = testtable

This works fine, but when I try to execute a command I get an error:

CREATE OR REPLACE FUNCTION merge_(text,n VARCHAR(32),s VARCHAR(32),val int) RETURNS VOID AS 
$$
 DECLARE
    _table ALIAS FOR $1;
 BEGIN
    UPDATE _table ....

ERROR: relation "_table" does not exist

score 1 · Accepted Answer · answered May 27 '14 at 10:28

Variable substitution in dynamic commands has to be done using the EXECUTE command:

EXECUTE 'UPDATE ' || quote_ident(_table) || ' ....';

You can check the docs here.

Wherever you want to use a parameter to insert a relation or column name into a dynamic command you should use the quote_ident() function. Other text elements (e.g. WHERE column_name = parameter_2) should be inserted with the quote_literal() function. Both of these functions help to avoid pesky conflicts (like double-quoting reserved words) and guard against SQL-injection.

postgresql pass table reference to the function

1 Answers1