[possibly] dumb question
[possibly] dumb question
От:
Dmitry Morozovsky <marck@rinet.ru>
Дата:
Hello colleagues, 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) Thanks in advance. Sincerely, D.Marck [DM5020, DM268-RIPE, DM3-RIPN] ------------------------------------------------------------------------ *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru *** ------------------------------------------------------------------------
Re: [possibly] dumb question
От:
Joel Burton <jburton@scw.org>
Дата:
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
Director of Information Systems, Support Center of Washington