SQL discussion

Troels Arvin | 1 Jul 2003 11:59

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo <at> postgresql.org so that your
      message can get through to the mailing list cleanly

headers

Robert Treat | 1 Jul 2003 18:28

Re: Immutable attributes?

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

(Continue reading)

headers

Troels Arvin | 1 Jul 2003 21:17

Re: Immutable attributes?

Hello,

On Tue, 2003-07-01 at 18:28, Robert Treat <xzilla <at> 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

(Continue reading)