From: | "Jules Alberts" <jules(dot)alberts(at)arbodienst-limburg(dot)nl> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Updating a date field |
Date: | 2002-08-20 06:43:44 |
Message-ID: | 200208200644.g7K6i5SP011210@artemis.cuci.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 19 Aug 2002 at 14:17, Stephan Szabo wrote:
> 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();
I do this too (with CURRENT_TIMESTAMP), and also include the last
person who has updated the row (current_user). You can also set these
fields to DEFAULT values and use the trigger to update them when the
row is updated.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian 'Dagurashibanipal' von Bidder | 2002-08-20 07:58:52 | Re: Multiple selects versus join |
Previous Message | Bruce Momjian | 2002-08-20 02:43:34 | Re: Temp tables |