Received: from mail.netizen.co.za (situs.netizen.co.za [216.156.254.66]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA41705 for ; Fri, 22 Oct 1999 12:06:55 -0400 (EDT) (envelope-from grant@situs.netizen.co.za) Received: from grant by mail.netizen.co.za with local (Exim 3.03 #2) id 11ehCD-0001MZ-00 for pgsql-sql@postgreSQL.org; Fri, 22 Oct 1999 12:05:37 -0400 Date: Fri, 22 Oct 1999 12:05:37 -0400 From: Grant Kaufmann To: pgsql-sql@postgresql.org Subject: Re: [SQL] Adding a rule to update a last_changed field Message-ID: <19991022120537.A5228@intekom.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 1.0pre3i Sender: Grant This does not work, as it still calls a new UPDATE and re-invokes the rule. > > I'm trying to use a rule to maintain a last_changed field. > > > > CREATE RULE rule1 AS > > ON UPDATE TO thetable > > DO UPDATE thetable SET last_changed = now() > > WHERE id = new.id; > > Try instead something like > > CREATE RULE rule1 AS > ON UPDATE TO thetable > DO INSTEAD UPDATE thetable SET > last_changed = now(), > field1 = new.field1, > field2 = new.field2, > ... > fieldn = new.fieldn > WHERE id = new.id; > > That is, use an INSTEAD rule and replace each field explicitly.