From: | Troels Arvin <troels(at)arvin(dot)dk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Immutable attributes? |
Date: | 2003-07-01 19:17:10 |
Message-ID: | 1057087030.15874.38.camel@localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello,
On Tue, 2003-07-01 at 18:28, Robert Treat <xzilla(at)users(dot)sourceforge(dot)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(at)arvin(dot)dk>
From | Date | Subject | |
---|---|---|---|
Next Message | scott.marlowe | 2003-07-01 20:13:31 | Re: need some help with a delete statement |
Previous Message | Matthew Hixson | 2003-07-01 17:55:06 | Re: need some help with a delete statement |