How to create a Postgres trigger to ensure field is non-updateable

Question

I'm trying to create a Postgres trigger to ensure after a column's value has been set, it cannot be updated (basically make it readonly). So far I've come up with the following draft trigger.

1) First, am I on the correct track?
2) Is there value in returning NEW?
3) Must I preface original_id with NEW.?

CREATE FUNCTION check_id_change() RETURNS TRIGGER AS $$
    BEGIN
        IF OLD.original_id <> NEW.original_id THEN
            RAISE EXCEPTION 'cannot change original_id'; 
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER client_update_trigger AFTER UPDATE ON client FOR EACH ROW
      EXECUTE PROCEDURE check_id_change();

Answer 1

Use a before update trigger.

Do you want to raise an exception or just have it silently not change the value?

Yes, you should return NEW.

Yes, you should prefix the column name with NEW or OLD.

You can use this if you don't want to raise an exception:

create function check_id_change() returns trigger language plpgsql as $$
begin

  new.original_id = old.original_id;

  return new;

end $$;

Answer 2

Create a BEFORE UPDATE on your specific column original_id.
NOTE since the application which is using the DB should be aware of its constraints, therefore it is better to raise an exception when the column is getting updated, in order to notify the application layer of a failure, instead of just ignoring the update.

CREATE OR REPLACE FUNCTION check_id_change()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  IF NEW."original_id" IS DISTINCT FROM OLD."original_id"
  THEN
    RAISE EXCEPTION '"original_id" column cannot get updated';
  END IF;

  RETURN NEW;
END;
$BODY$ LANGUAGE PLPGSQL;

CREATE TRIGGER client_update_trigger
BEFORE UPDATE OF "original_id"
  ON "client"
FOR EACH ROW
EXECUTE PROCEDURE check_id_change();

Or using WHEN condition

CREATE OR REPLACE FUNCTION check_id_change()
  RETURNS TRIGGER AS
$BODY$
BEGIN
  RAISE EXCEPTION '"original_id" column cannot get updated';
END;
$BODY$ LANGUAGE PLPGSQL;

CREATE TRIGGER client_update_trigger
BEFORE UPDATE OF "original_id"
  ON "client"
FOR EACH ROW
WHEN (NEW."original_id" IS DISTINCT FROM OLD."original_id")
EXECUTE PROCEDURE check_id_change();

NOTE use IS DISTINCT FROM comparison instead of <> or != unless the column has NOT NULL or you're sure that it's value will never be NULL.