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 use the built-in JSON functions json_populate_record() or jsonb_populate_record() for a similar effect. But that (UPDATE!) wasn't documented before Postgres 13. Now it's a documented feature.

In earlier versions, the sure way was 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
 LANGUAGE plpgsql 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$;

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
 LANGUAGE plpgsql AS
$func$
BEGIN
 NEW.date_price:= OLD.date_price; -- unconditionally
 RETURN NEW;
END
$func$;

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.

• chamini2: I think I'll byte the bullet, thanks for the advice.
• Erwin Brandstetter: @chamini2: What a typo, eh? That one can stay. :)
• Evan Carroll: @ErwinBrandstetter is json_populate_record in Pg for good? it's documented now in 9.6
• Erwin Brandstetter: @EvanCarroll: The function json_populate_record() has always been documented. But the feature we need here is not: pass a pre-populated record to merge columns from old and new. Follow the 2nd link above for details.
• Erwin Brandstetter: @EvanCarroll: It's trivial to update fields of row types. But doing it dynamically is not as simple - even if still possible even without hstore and json. (Same link as above.) Ask a question if anything remains unclear. We are over-extending comments here.