Re: Immutable attributes?
От | Troels Arvin |
---|---|
Тема | Re: Immutable attributes? |
Дата | |
Msg-id | 1057087030.15874.38.camel@localhost обсуждение исходный текст |
Ответ на | Immutable attributes? (Troels Arvin <troels@arvin.dk>) |
Список | pgsql-sql |
Hello, On Tue, 2003-07-01 at 18:28, Robert Treat <xzilla@users.sourceforge.net> wrote: > > want to make sure that the "crated" attribut for a tuple is > > not changed once it has been set. > > > > I'm thinking about implementing it through a trigger, but is there a > > better way to create such "immutable" attributes? > > I don't know if it's "better", but this is one of the things people find > the RULE system really handy for. I thought about using the rule system for that. However:- I would like to be able to throw an exception if an immutable attribute is changed; it seems that can't be done with the rule system(?)- it seems that RULEs are a PostgreSQL-only phenomenon;I try to keep my SQL more portable than that > Check the docs, I believe there are examples of this. I haven't been able to find any related examples. Anyways, I have now found a way to implement my immutable timestamp fields using a stored procedure and a trigger: create function create_time_unchanged() returns trigger as ' begin if old.time_created <> new.time_created then raise exception ''time_created may not be changed: % <> %'', old.time_created, new.time_created ; end if; return new; end;' language 'plpgsql'; create trigger ensure_create_time_unchanged before update on transaction_pbs for each row execute procedure create_time_unchanged(); Now, let's say that the "transaction" relation has a field "time_created" of type timestamp with time zone and that a record with time_created=2003-07-01 20:56:11.393664+02 : => update transaction => set time_created='2003-07-01 20:56:11.393664+02'::timestamptz => where order_id=1000; -- NOTE: No change. UPDATE 1 => update transaction => set time_created='2003-07-01 20:56:00+02'::timestamptz => where order_id=1000; -- NOTE: Changed. ERROR: time_created may not be changed: 2003-07-01 20:56:11.393664+02 <> 2003-07-01 20:56:00+02 So things work. -- Troels Arvin <troels@arvin.dk>
В списке pgsql-sql по дате отправления: