Question 89175 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql, trigger that prevents updates of all columns except choosen one

I’d like to create trigger function, without specifying by name the other columns that can’t be updated.

Example:

Create table test(id integer, name text, amount numeric);

I’d like to prevent updates of any columns other than amount, but to avoid specifying by name columns id and name like this

IF NEW.id <> OLD.id or NEW.name <> OLD.name THEN RAISE EXCEPTION 'UPDATE DISALLOWED'; END IF;

I would like to apply this trigger to multiple tables, that’s why I don’t want to specify the other columns.

Maybe it’s somehow possible using row level security? But I need to prevent also postgres role from the updates. I haven’t found how to do it.

@Edit
After hints I think I’m close to the solution, but I’m getting syntax error while trying to execute generated IF statement.
https://dbfiddle.uk/vp85wHgc

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I figured out following solution:

    CREATE OR REPLACE FUNCTION public.table_update_guard()
  RETURNS trigger AS
$BODY$
  DECLARE  rec record; 
_columns text;  _old text; _new text;
  BEGIN  

FOR rec IN
SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' 
    AND table_name = TG_TABLE_NAME  and column_name <> 'amount' 
LOOP
_columns = concat(_columns , '$1.' , rec.column_name , ',');
END LOOP;

_columns = left(_columns, -1);

EXECUTE format('SELECT concat(%s )', _columns) into _old
   USING OLD;
EXECUTE format('SELECT concat(%s )', _columns) into _new
   USING NEW;

raise notice '
_new_ % 
_old_ %', _new, _old ;

IF _new <> _old then raise exception 'UPDATE NOT ALLOWED!!';
    END IF;

RETURN NEW;
END;
 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

  2. 0

    You can do something like this, but identifying what the changed column is probably does need dynamic SQL.

    CREATE OR REPLACE FUNCTION z_trig()
RETURNS TRIGGER
AS $$
DECLARE
    old_r RECORD;
    new_r RECORD;
BEGIN
    old_r := OLD;
    new_r := NEW;
    old_r.foo := new_r.foo;
    IF old_r.* IS DISTINCT FROM new_r.* THEN
        RAISE EXCEPTION 'Forbidden change!';
    END IF;

    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
    Login or Signup to reply.
  3. 0

    Use @RichardHuxton’s answer. But if you want dynamic SQL, you can get a list of columns as a string list. Then split them up. The table name seems like a predefined variable TG_TABLE_NAME for a trigger function.

    To get the column list, this is the typical way. However, the ORDER BY here will not give you the order of the columns correctly unless you list the columns explicitly. 99/100 the ORDER BY will be correct, but do not trust it.

    SELECT column_name
  FROM information_schema.columns
 WHERE table_schema = TG_TABLE_SCHEMA
   AND table_name   = TG_TABLE_NAME
     ;

    If you want the order YOU built the table with, rather than the order in which * displays the columns in Postgres, you need to look at this. attnum is the order index. attname is the column name.

    SELECT pg_attribute.attname 
FROM pg_catalog.pg_attribute 
INNER JOIN pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid 
INNER JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace 
WHERE pg_namespace.nspname = TG_TABLE_SCHEMA and pg_class.relname = TG_TABLE_NAME 
ORDER BY pg_attribute.attnum

    Reference:

    https://www.postgresql.org/docs/current/plpgsql-trigger.html

    Dynamic SQL Example:

    EXECUTE 'SELECT * FROM ' || TG_TABLE_NAME

    Anything in the string after EXECUTE is what you would normally have as valid SQL code. So the IF statement goes inside there. If the IF statement contains values assigned to variables, then concatenate the variables to the string as I’ve done with TG_TABLE_NAME.

    So given your question code, we’d have this.

    EXECUTE 'IF NEW.' || ID_COLUMN || ' <> OLD' || '.' || ID_COLUMN || ' or NEW.' || NAME_COLUMN + ' <> OLD.' || NAME_COLUMN || ' THEN RAISE EXCEPTION ''UPDATE DISALLOWED''; END IF;'

    Now take this a step further and find the ID_COLUMN and NAME_COLUMN variables using the query above to find the column names. If id is ALWAYS the first column and name is ALWAYS the second column, assign the list of column names (in proper order) returned to a variable COLUMN_LIST using the result set to list function (this is your homework). Then find the function(s) in Postgres to grab piece 1 and assign to ID_COLUMN variable and piece 2 to NAME_COLUMN.

    Error in your code for the IF statement was not having a space before THEN. And I pushed everything to one line.

    execute 'IF ' || _sql || ' THEN raise exception ''UPDATE FORBIDDEN!'' END IF;';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search