Assignment of a column with dynamic column name

Question

I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following:

CREATE OR REPLACE FUNCTION prevent_column_update() RETURNS TRIGGER AS $$
  DECLARE
    col TEXT := TG_ARGV[0];
  BEGIN
    EXECUTE format('SELECT ($1).%I INTO ($2).%I', col, col) USING OLD, NEW;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

And to be used like:

CREATE TRIGGER request_protect_date_price_value
  BEFORE UPDATE OF date_price ON requests
  FOR EACH ROW EXECUTE PROCEDURE prevent_column_update('date_price');

But when updating it fails with:

ERROR:  syntax error at or near "("
LINE 1: SELECT ($1).date_price INTO ($2).date_price
                                    ^
QUERY:  SELECT ($1).date_price INTO ($2).date_price

Answer 1

The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE.

And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there are ways around this limitation:

Proof of concept

• Assign to NEW by key in a Postgres trigger
• How to set value of composite variable field using dynamic SQL

You can (ab)use the built-in JSON function json_populate_record() for a similar trick, but that's currently not documented and may be removed in future versions of Postgres.

The sure way is to use the documented #= operator of the additional hstore module. Install the module once per database with

CREATE EXTENSION IF NOT EXISTS hstore;

Then:

CREATE OR REPLACE FUNCTION prevent_column_update()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col text := quote_ident(TG_ARGV[0]);
   _old_val text;
   _new_val text;
BEGIN
   EXECUTE format('SELECT $1.%1$I, $2.%1$I', _col)
   INTO    _old_val, _new_val  -- part of plpgsql command
   USING   OLD, NEW;

   IF _old_val IS DISTINCT FROM _new_val THEN  -- only if it actually changed
      NEW := NEW #= hstore(_col, _old_val);    -- hstore operator #=
   END IF;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Note that hstore operates with text strings. Values for other data types are cast to text and back, which works for any data type I can think of. But it might cause problems for some types (like rounding errors for floating point numbers).

And this trigger definition to make the case complete:

CREATE TRIGGER tbl_upbef_nope
BEFORE UPDATE ON tbl  -- your table here
FOR EACH ROW
EXECUTE PROCEDURE prevent_column_update('date_price');

The column name is case sensitive in this example, since it is passed as string and not as identifier.

What I would do

Just write a new plain trigger function without dynamic SQL for each table. Less hassle, better performance. Byte the bullet on code duplication:

CREATE OR REPLACE FUNCTION prevent_column_update()
  RETURNS TRIGGER AS
$func$
BEGIN
   NEW.date_price:= OLD.date_price;  -- unconditionally
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER tbl_upbef_nope
BEFORE UPDATE OF date_price ON tbl  -- your column and table here
FOR EACH ROW EXECUTE PROCEDURE prevent_column_update();  -- no param

I moved the check to the trigger itself, so the function is not even executed unless the column is updated. Note that this can be circumvented by additional triggers on the same table because (quoting the manual):

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

So if you cannot rule out such additional triggers, fire the trigger on UPDATE unconditionally and check for changes in the trigger function instead.