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 55C70D7080 for ; Sun, 20 Nov 2005 19:41:16 -0400 (AST) 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 75117-08 for ; Sun, 20 Nov 2005 23:41:18 +0000 (GMT) X-Greylist: delayed 00:05:10.012857 by SQLgrey- Received: from rwcrmhc11.comcast.net (rwcrmhc11.comcast.net [216.148.227.117]) by svr1.postgresql.org (Postfix) with ESMTP id C574CD6E94 for ; Sun, 20 Nov 2005 19:41:13 -0400 (AST) Received: from [192.168.0.9] (c-24-17-161-80.hsd1.wa.comcast.net[24.17.161.80]) by comcast.net (rwcrmhc11) with ESMTP id <2005112023360401300c0rr7e>; Sun, 20 Nov 2005 23:36:05 +0000 From: Adrian Klaver Reply-To: aklaver@comcast.net To: pgsql-sql@postgresql.org, Subject: Re: Is it possible to redirect an update/insert/delete to a different table? Date: Sun, 20 Nov 2005 15:35:57 -0800 User-Agent: KMail/1.6.2 Cc: "'Andreas Kretschmer'" References: In-Reply-To: MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200511201535.57300.aklaver@comcast.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=2.319 required=5 tests=[DNS_FROM_RFC_POST=1.44, DNS_FROM_RFC_WHOIS=0.879] X-Spam-Score: 2.319 X-Spam-Level: ** X-Archive-Number: 200511/177 X-Sequence-Number: 23428 On Sunday 20 November 2005 09:15 am, Andy Ballingall wrote: It works because of the way updates are done. When you do an update two versions of the row exist. The OLD version is the row as it existed before you updated. The NEW version contains the entire version with the update changes. The key thing to remember is the the NEW version contains both those fields that have changed as well as those that have not. So the UPDATE rule just passes along all the fields named in it regardless of whether they changed or not. It would be a good idea to read the following section of the manual (http://www.postgresql.org/docs/8.0/interactive/rules.html) as it explains when the rule picks up the values in the fields. This differs according to the type of rule. > >Try it. [snipped example] > > Ah. Basically, you set up the rule to assign every column, and if the > update doesn't redefine some columns, then it still works. I didn't > understand that you could get the rule to work generically like this. > > I'll presume that the rule will need amending if the table column > definition later changes. (E.g. if I add 'stalk_length' to my apples and > pears tables)... > > Thanks very much for your help. > > Andy > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- Adrian Klaver aklaver@comcast.net