Dynamically access column value in record

Question

Is it possible to dynamically access a column value from a record by its name?

I'm writing a trigger function that executes a dynamic SQL command and I would like to dynamically extract a column value from a NEW record by column name.

Here's a simplified example of what I'm trying to do:

$$
DECLARE
   command text := 'UPDATE $1 SET $2 = $3';
   myColumn := 'votes'
BEGIN
   EXECUTE command using 'anotherTable', myColumn, NEW.myColumn;
END
$$

Answer 1

That's possible, but the USING clause of EXECUTE can only pass values, while identifiers like table and column names must be concatenated in the command string. (Be wary of SQL injection!) Using format() it could work like this:

CREATE FUNCTION ... AS
$func$
DECLARE
   _command text := 'UPDATE %I SET %I = $1 WHERE ....';  -- add WHERE condition
   _col text := 'votes';
BEGIN
   EXECUTE format(_command, 'anotherTable', _col)
   USING  NEW.myColumn;
END
$func$;

Fixed a couple of minor problems in passing.

It must be mentioned that NEW is only available in trigger functions.

Be aware that 'anotherTable' is case sensitive here (being concatenated safely with double-quotes from a string), while NEW.myColumn is not (processed as unquoted identifier). Always use legal, lower case, unquoted identifiers in Postgres to make your life easier.

Related answers with more explanation and links:

• PL/pgSQL: General Way to Update N Columns in Trigger?
• INSERT with dynamic table name in trigger function
• Table name as a PostgreSQL function parameter
• Are PostgreSQL column names case-sensitive?

---

To dynamically extract a column value from a NEW record by column name.

... you can use the hstore #= operator:

• How to set value of composite variable field using dynamic SQL

Or you can make it work with standard features of dynamic SQL as well:

CREATE FUNCTION ... AS
$func$
DECLARE
   _col text := 'votes';
   _new_col text := 'column_name_in_new';  -- case-sensitive column name here
BEGIN
   EXECUTE format(
       'UPDATE %I SET %I = $1.%I WHERE ... '  -- add WHERE condition
     , 'anotherTable', _col, _new_col)
   USING  NEW;  -- pass whole row
END
$func$;

Related:

• Creating a trigger for child table insertion returns confusing error