Update timestamp on update

From: Jeff Williams <jeffw(at)globaldial(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Update timestamp on update
Date: 2005-10-13 01:12:34
Message-ID: 434DB482.1000205@globaldial.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-sql

I have a table like:

CREATE TABLE products (
id int,
status int,
last_status_change timestamp DEFAULT now()
);

What I would like is that whenever the status is changed the
last_status_change timestamp is updated to the current time. I have had
a look at the rules and what I want would be similar to:

CREATE RULE last_status_change AS ON UPDATE
TO products WHERE NEW.status <> OLD.status
DO UPDATE products SET last_status_change = now() WHERE id = OLD.id;

Except of course that the above is recursive and doesn't work.

How can I do this?

Jeff

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Tom Lane 2005-10-13 01:44:49 Re: Update timestamp on update
Previous Message Bruce Momjian 2005-10-12 14:55:17 Re: COPY example for partial tables

Browse pgsql-sql by date

  From Date Subject
Next Message Greg Stark 2005-10-13 01:13:29 Re: pg, mysql comparison with "group by" clause
Previous Message Tom Lane 2005-10-12 23:00:05 Re: Text->Date conversion in a WHERE clause