Received: from biology.nmsu.edu (biology.NMSU.Edu [128.123.5.72]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA49898 for ; Fri, 22 Oct 1999 12:43:06 -0400 (EDT) (envelope-from brook@biology.nmsu.edu) Received: (from brook@localhost) by biology.nmsu.edu (8.8.8/8.8.8) id KAA04832; Fri, 22 Oct 1999 10:41:18 -0600 (MDT) Date: Fri, 22 Oct 1999 10:41:18 -0600 (MDT) Message-Id: <199910221641.KAA04832@biology.nmsu.edu> X-Authentication-Warning: biology.nmsu.edu: brook set sender to brook@biology.nmsu.edu using -f From: Brook Milligan To: grant@intekom.com CC: pgsql-sql@postgreSQL.org In-reply-to: <19991022120537.A5228@intekom.com> (message from Grant Kaufmann on Fri, 22 Oct 1999 12:05:37 -0400) Subject: Re: [SQL] Adding a rule to update a last_changed field References: <19991022120537.A5228@intekom.com> Oops. This does not work, as it still calls a new UPDATE and re-invokes the rule. You are right. Your initial mention of using rules made me think of all the cases I have used this technique for with views. I forgot that the two tables are the same. What I meant to suggest (which may not be relevant if you really have only one table) was create a rule for a view of your table that will update the last_changed field in the underlying table. I have found that to be very useful in a bunch of situations where I commonly have one table with lots of different views and different ways of inserting/deleting/updating as appropriate to each view. Then the redirection via an INSTEAD rule and setting last_* fields with rules is valuable. Sorry about missing the fact that both of your tables were the same. > CREATE RULE rule1 AS > ON UPDATE TO thetable <-- for views these are > DO INSTEAD UPDATE thetable SET <-- not the same table > last_changed = now(), > field1 = new.field1, > field2 = new.field2, > ... > fieldn = new.fieldn > WHERE id = new.id; Cheers, Brook