From: | Joel Burton <jburton(at)scw(dot)org> |
---|---|
To: | Dmitry Morozovsky <marck(at)rinet(dot)ru> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: [possibly] dumb question |
Date: | 2001-05-04 19:21:18 |
Message-ID: | Pine.LNX.4.21.0105041517070.30777-100000@olympus.scw.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Fri, 4 May 2001, Dmitry Morozovsky wrote:
> sorry for dumb question, but can anybody advise me what default statement
> should I write to fill timestamp column in record to the time of
> insertion. 'now'::timestamp leads to timestamp of database creation for
> all records, 'current' can not be converted to timestamp...
>
> also, I'm rather new to triggers, can anybody help me how should look the
> trigger for tracking last modification time of the record (yes, I do know
> I can simply put 'now' in update statement, but I want to disable ability
> to change such column by hand)
current_timestamp will work, and is a standard as well. No parenthesis
after it.
Make a procedure that (a) updates the changed field, and disallows
changes. Something like:
create table foo (
...
chgat timestamp not null default current_timestamp
);
create function foo_update() returns opaque as '
begin
if new.chgat <> old.chgat then
raise error ''Do not change timestamp fields by hand'';
end if;
new.chgat = current_timestamp;
return new;
end;
' language 'plpgsql';
and use that as your AFTER UPDATE trigger.
BTW, this question should be sent to pgsql-general or
pgsql-novice. pgsql-admin is mostly for administrative info about
PostgreSQL.
--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington
From | Date | Subject | |
---|---|---|---|
Next Message | Joel Burton | 2001-05-04 20:13:47 | Re: Queries |
Previous Message | Dmitry Morozovsky | 2001-05-04 17:39:22 | [possibly] dumb question |