Обсуждение: [possibly] dumb question

Поиск
Список
Период
Сортировка

[possibly] dumb question

От
Dmitry Morozovsky
Дата:
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
Дата:
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