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@scw.org>
Director of Information Systems, Support Center of Washington