Re: Updating a date field

On Mon, 19 Aug 2002, Dan C wrote:

> I have a date field called "updated" with a default of now.
> When I INSERT data it the field displays today's date.
> My problem is when I UPDATE the row it doesn't change unless
> I update the field manually like this:
> UPDATE person SET email='doe@foo.com',updated='now()' WHERE
> person_id=614486;
> Is there an automatic way?
> I tried doing it with a rule, which returned
> ERROR:  query rewritten 10 times, may contain cycles
> I tried a CONSTRAINT and TRIGGER without any luck either.

A before trigger should do it for you (untested)

create function setupdated() returns opaque as '
begin
 NEW.updated := now();
 return NEW;
end;'
language 'plpgsql';

create trigger person_setupdated before update on
 person for each row execute procedure setupdated();