Immutable attributes?

From: Stephane Bortzmeyer <bortzmeyer(at)nic(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: Immutable attributes?
Date: 2005-04-24 19:26:49
Message-ID: 20050424192649.GA16335@sources.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

To protect the database from programming errors (there is a team
working on the project and some beginners may produce bugs), I would
like to flag some attributes as immutable, meaning non modifiable in
an UPDATE. (Typical examples are ID or creation time.)

Currently, I use triggers:

CREATE OR REPLACE FUNCTION check_immutable() RETURNS TRIGGER
AS 'BEGIN
IF NEW.id != OLD.id OR NEW.created != OLD.created THEN
RAISE EXCEPTION ''Change not allowed in that table'';
END IF;
RETURN NEW;
END;'
LANGUAGE PLPGSQL;

CREATE TRIGGER check_immutable
BEFORE UPDATE ON MyTable
FOR EACH ROW
EXECUTE PROCEDURE check_immutable();

It is quite painful, since I need a function (with the list of
immutable attributes) and a trigger per table. If I INHERITS from a
table, triggers on the parent table are not called if I update the
child table.

Is there a better way? I do not find a constraint IMMUTABLE for
attributes.

PostgreSQL 7.4, switching to 8.0 would be difficult.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Zlatko Matic 2005-04-24 20:16:14 Calculated bigserial column in a view
Previous Message Rich Shepard 2005-04-24 19:10:22 Re: Primary Key and Indices