postgresql pass table reference to the function [duplicate]

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

Patrick · Accepted Answer · 2014-05-27 10:28:04Z

1

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.

answered May 27, 2014 at 10:28

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql pass table reference to the function [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related