| 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: | Whole Thread | Raw Message | 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.
| 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 |