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
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.