Thread: Immutable attributes?

Hello,

I have a table like this:

create table test ( "test_id" serial primary key, "created" timestamp with time zone    default current_timestamp
check(created= current_timestamp), "some_datum" int not null
 
);

My question concerns the "created" attribute: I want this to reflect when
the tuple was craeated; and I want to make sure that the timestamp is not
wrong. That will work with the above schema. However, I also 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?

/Troels

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>

On Tue, 2003-07-01 at 05:59, Troels Arvin wrote:
> Hello,
> 
> I have a table like this:
> 
> create table test (
>   "test_id" serial primary key,
>   "created" timestamp with time zone
>      default current_timestamp
>      check(created = current_timestamp),
>   "some_datum" int not null
> );
> 
> My question concerns the "created" attribute: I want this to reflect when
> the tuple was craeated; and I want to make sure that the timestamp is not
> wrong. That will work with the above schema. However, I also 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. Check the docs, I believe there are
examples of this.

Robert Treat 
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL