Re: [possibly] dumb question

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