Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB6LWSm03417 for ; Thu, 6 Dec 2001 16:32:28 -0500 (EST) (envelope-from reedstrm@rice.edu) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 5308868A1D; Thu, 6 Dec 2001 15:32:29 -0600 (CST) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 97B1E68A17; Thu, 6 Dec 2001 15:32:28 -0600 (CST) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 16C684-00036F-00; Thu, 06 Dec 2001 15:32:28 -0600 Date: Thu, 6 Dec 2001 15:32:28 -0600 From: "Ross J. Reedstrom" To: Oleg Lebedev Cc: Postgres SQL Mailing List Subject: Re: view rules Message-ID: <20011206153228.E10995@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , Oleg Lebedev , Postgres SQL Mailing List References: <3C0FD268.DFFA9CB8@waterford.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3C0FD268.DFFA9CB8@waterford.org> User-Agent: Mutt/1.3.18i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200112/82 X-Sequence-Number: 5905 On Thu, Dec 06, 2001 at 01:17:44PM -0700, Oleg Lebedev wrote: > Hi everybody, > I am trying to create an update rule for a view. It's created fine, and > I can find it in pg_rules, but when I try to update the view, I get the > usual error: > ERROR: Cannot update a view without an appropriate rule. > > My rule definitions is as follows: > CREATE RULE update_priority AS ON UPDATE TO progress_report > WHERE NEW.priority != OLD.priority > DO INSTEAD UPDATE activity SET priority=NEW.priority > WHERE activity.productcode = OLD.product_code > AND activity.actname=OLD.component; > > I am trying to update the view as follows: > update progress_report set priority=2 where product_code='m3' and > component='act'; > The View/Rule system is very picky: you have to have rules to cover _every_ possible update case before it'll allow any to go through. The usual way aroun this is to create a 'do nothing' rule with no WHERE clause: ifs_test=# create rule update_any as ON UPDATE TO progress_report DO INSTEAD NOTHING; ifs_test=# select * from progress_report; priority | product_code | component ----------+--------------+----------- 1 | 3 | act (1 row) ifs_test=# update progress_report set priority=2 where product_code='3' and component='act'; UPDATE 1 ifs_test=# select * from progress_report; priority | product_code | component ----------+--------------+----------- 2 | 3 | act (1 row) (I fudged your product code, since I'd created an int in my test case: if you'd sent along schema for the table, my test would have gone _much_ faster) Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005