You want to effectively "lock" a field so that after the record has been inserted, that field can never be changed (a creation_date field for example).
You use an UPDATE trigger that masks the field ref. That calls a function that either discards the change or raises an error if there is a change to the protected field ref.
If you have the Postgres source-code there is a more general solution in contrib/noupdate that uses C.
To make sure the dy_timestamp field cannot be modified use:
CREATE FUNCTION noupd_ts() RETURNS opaque AS ' BEGIN NEW.dy_timestamp = OLD.dy_timestamp RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER diary_noupd_ts_trig BEFORE UPDATE ON diary FOR EACH ROW EXECUTE PROCEDURE noupd_ts(); |
The function silently discards any changes to the create_date field if users update the diary table. The function/trigger pair needs to be repeated for each column to protect (or see the contrib/noupdate solution).