1

Is it possible to create such trigger function, without specifying by name the other columns that shouldn't be affected?

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.

2
  • 1
    You could use GRANT at the column level: GRANT UPDATE(colname) ON TABLE tablename TO somerole. Mar 23, 2023 at 21:32
  • Is it possible to revoke updates from postgres role? Or do it using row level security?
    – sh4rkyy
    Mar 29, 2023 at 12:59

1 Answer 1

0

You could update and return the OLD record from the trigger function.

BEGIN
    OLD.amount := NEW.amount;
    RETURN OLD;
END;

To raise an exception if an attempt has been made to update other columns, you could check if the records are distinct from each other.

BEGIN
    OLD.amount := NEW.amount;

    IF (NEW IS DISTINCT FROM OLD) THEN
         RAISE EXCEPTION 'UPDATE of columns other than "amount" is forbidden';
    END IF;

    RETURN OLD;
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.