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(at)foo(dot)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();