Postgres trigger to avoid update in a column

Question

I have a postgres database and also I have a table called product. In this table I have an autoincrement column named auxId (not table primary key). I want to avoid any update on this column. How can this be done using a postgres trigger to avoid updates on auxid? I tried:

   #trigger to avoid updates on nameId
CREATE OR REPLACE FUNCTION stop_change_on_auxId()
  RETURNS trigger AS
$BODY$
BEGIN
 IF NEW.auxId <> OLD.auxId THEN

 END IF;

 RETURN NEW;
END;
$BODY$


CREATE TRIGGER avoid_auxid_changes
  BEFORE UPDATE
  ON product
  FOR EACH ROW
  EXECUTE PROCEDURE stop_change_on_auxId();

but I believe this will stop the update for the whole row. I just need to avoid the update on the auxId field but allow any other update on its row.

Answer 1

If you basically want to make the auxid column immutable to the outside world, here you go:

CREATE OR REPLACE FUNCTION stop_change_on_auxId()
  RETURNS trigger AS
$BODY$
BEGIN
  -- always reset the auxId to the value already stored
  NEW.auxId := OLD.auxId;
  RETURN NEW;
END;
$BODY$


CREATE TRIGGER avoid_auxid_changes
  BEFORE UPDATE
  ON product
  FOR EACH ROW
  EXECUTE PROCEDURE stop_change_on_auxId();

Answer 2

Sure, just throw an error when the column is changed:

CREATE FUNCTION noupdate() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF NEW.auxid <> OLD.auxid THEN
      RAISE EXCEPTION 'not allowed';
   END IF;
   RETURN NEW;
END;$$;

CREATE TRIGGER noupdate
   BEFORE UPDATE ON product FOR EACH ROW
   EXECUTE PROCEDURE noupdate();