How to use dynamic column names in an UPDATE or SELECT statement in a function?

Question

In PostgreSQL 9.1, PL/pgSQL, given a query:

select fk_list.relname from ...

where relname is of type name (e.g., "table_name").

How do you get the appropriate value for "relname" that can be used directly in an UPDATE statement as:

Update <relname> set ...

within the PL/pgSQL script?

Using quote_ident(r.relname) as:

Update quote_ident(r.relname) Set ...

fails with:

syntax error at or near "(" LINE 55: UPDATE quote_ident(r.relname) ....

The complete code I am working with:

CREATE FUNCTION merge_children_of_icd9 (ocicd9 text, ocdesc text, ncicd9 text, ncdesc text) RETURNS void AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN WITH fk_actions ( code, action ) AS ( VALUES ('a', 'error'), ('r', 'restrict'), ('c', 'cascade'), ('n', 'set null'), ('d', 'set default') ), fk_list AS ( SELECT pg_constraint.oid AS fkoid, conrelid, confrelid::regclass AS parentid, conname, relname, nspname, fk_actions_update.action AS update_action, fk_actions_delete.action AS delete_action, conkey AS key_cols FROM pg_constraint JOIN pg_class ON conrelid = pg_class.oid JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code WHERE contype = 'f' ), fk_attributes AS ( SELECT fkoid, conrelid, attname, attnum FROM fk_list JOIN pg_attribute ON conrelid = attrelid AND attnum = ANY(key_cols) ORDER BY fkoid, attnum ), fk_cols_list AS ( SELECT fkoid, array_agg(attname) AS cols_list FROM fk_attributes GROUP BY fkoid ) SELECT fk_list.fkoid, fk_list.conrelid, fk_list.parentid, fk_list.conname, fk_list.relname, fk_cols_list.cols_list FROM fk_list JOIN fk_cols_list USING (fkoid) WHERE parentid = 'icd9'::regclass LOOP RAISE NOTICE 'now in loop. relname is %', quote_ident(r.relname); RAISE NOTICE 'cols_list[1] is %', quote_ident(r.cols_list[1]); RAISE NOTICE 'cols_list[2] is %', quote_ident(r.cols_list[2]); RAISE NOTICE 'now doing update'; UPDATE quote_ident(r.relname) SET r.cols_list[1] = ncicd9, r.cols_list[2] = ncdesc WHERE r.cols_list[1] = ocicd9 AND r.cols_list[2] = ocdesc; RAISE NOTICE 'finished update'; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql VOLATILE; -- select merge_children_of_icd9('', 'aodm type 2', '', 'aodm, type 2');

I'm sure this kind of thing is done often, but I can't seem to find anything like it using PostgreSQL. Is there a better way?

@a_horse_with_no_name O.k. Please see Edit of question. Any critique is more then welcome :-) Thanks. — Alan Wayne
– Alan Wayne, Commented Jun 9, 2015 at 22:38

Patrick · Accepted Answer · 2015-06-10 01:44:58Z

In an UPDATE statement in PL/pgSQL, the table name has to be given as a literal. If you want to dynamically set the table name and the columns, you should use the EXECUTE command and paste the query string together:

EXECUTE 'UPDATE ' || quote_ident(r.relname) || ' SET ' || quote_ident(r.cols_list[1]) || ' = $1, ' || quote_ident(r.cols_list[2]) || ' = $2' || ' WHERE ' || quote_ident(r.cols_list[1]) || ' = $3 AND ' || quote_ident(r.cols_list[2]) || ' = $4' USING ncicd9, ncdesc, ocicd9, ocdesc;

The USING clause can only be used for substituting data values, as shown above.

Erwin Brandstetter · Accepted Answer · 2023-02-11 13:18:45Z

You need dynamic SQL with EXECUTE like @Patrick already provided. However, both your function and the EXECUTE part can be much simpler. In particular, use format() to concatenate longer query strings safely (available since pg 9.1):

CREATE OR REPLACE FUNCTION merge_children_of_icd9 (_ocicd9 text, _ocdesc text , _ncicd9 text, _ncdesc text) RETURNS void LANGUAGE plpgsql AS $func$ DECLARE _sql text; BEGIN FOR _sql IN SELECT format('UPDATE %3$s SET %1$I = $3 , %2$I = $4 WHERE %1$I = $1 AND %2$I = $2' , x.cols[1], x.cols[2], x.conrelid::regclass::text) FROM ( SELECT c.conrelid, array_agg(a.attname ORDER BY a.attnum) AS cols FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey) WHERE c.confrelid = 'icd9'::regclass AND c.contype = 'f' GROUP BY c.oid, c.conrelid ORDER BY c.oid ) x LOOP -- RAISE NOTICE '%', _sql; -- debug? EXECUTE _sql USING _ocicd9, _ocdesc, _ncicd9, _ncdesc; END LOOP; END $func$;

The function errors out if the FK constraint does not span at least two columns or if the data type of the columns is not compatible with text. May or may not be as intended.

Details on how to pass identifiers safely and execute dynamic SQL:

Collectives™ on Stack Overflow

How to use dynamic column names in an UPDATE or SELECT statement in a function?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related