X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 95F2DD828A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 12 Oct 2005 22:12:40 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 16604-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 13 Oct 2005 01:12:33 +0000 (GMT)
Received: from dagon.globaldial.com (dagon.globaldial.com [202.74.164.46])
	by svr1.postgresql.org (Postfix) with ESMTP id 7B83AD81F4
	for <pgsql-sql@postgresql.org>; Wed, 12 Oct 2005 22:12:35 -0300 (ADT)
Received: from [192.168.10.72] (satellite [203.24.54.17])
	by dagon.globaldial.com (Postfix) with ESMTP id 4488B569E6
	for <pgsql-sql@postgresql.org>; Thu, 13 Oct 2005 09:12:35 +0800 (WST)
Message-ID: <434DB482.1000205@globaldial.com>
Date: Thu, 13 Oct 2005 09:12:34 +0800
From: Jeff Williams <jeffw@globaldial.com>
User-Agent: Debian Thunderbird 1.0.7 (X11/20051001)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Update timestamp on update
X-Enigmail-Version: 0.92.0.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 required=5 tests=[none]
X-Spam-Level: 
X-Archive-Number: 200510/115
X-Sequence-Number: 22968

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