pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedImmutable attributes?
3+ messages / 2 participants
[nested] [flat]
* Immutable attributes?
@ 2003-07-01 09:59 Troels Arvin <troels@arvin.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Troels Arvin @ 2003-07-01 09:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Immutable attributes?
@ 2003-07-01 16:28 Robert Treat <xzilla@users.sourceforge.net>
parent: Troels Arvin <troels@arvin.dk>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Treat @ 2003-07-01 16:28 UTC (permalink / raw)
To: Troels Arvin <troels@arvin.dk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Immutable attributes?
@ 2003-07-01 19:17 Troels Arvin <troels@arvin.dk>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 3+ messages in thread
From: Troels Arvin @ 2003-07-01 19:17 UTC (permalink / raw)
To: 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>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-07-01 19:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-01 09:59 Immutable attributes? Troels Arvin <troels@arvin.dk>
2003-07-01 16:28 ` Robert Treat <xzilla@users.sourceforge.net>
2003-07-01 19:17 ` Troels Arvin <troels@arvin.dk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox