Received: from mail1.waterford.org (mail1.waterford.org [205.124.117.6]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB6Lrlm04172 for ; Thu, 6 Dec 2001 16:53:47 -0500 (EST) (envelope-from olebedev@waterford.org) Received: from waterford.org ([205.124.138.93]) by mail1.waterford.org (Netscape Messaging Server 3.56) with ESMTP id 555; Thu, 6 Dec 2001 14:44:10 -0700 Message-ID: <3C0FEDC6.23389A8@waterford.org> Date: Thu, 06 Dec 2001 15:14:30 -0700 From: Oleg Lebedev X-Mailer: Mozilla 4.72 [en] (Windows NT 5.0; U) X-Accept-Language: en MIME-Version: 1.0 To: "Ross J. Reedstrom" CC: Postgres SQL Mailing List Subject: Re: view rules References: <3C0FD268.DFFA9CB8@waterford.org> <20011206153228.E10995@rice.edu> Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit X-Archive-Number: 200112/83 X-Sequence-Number: 5906 Thanks a lot Ross, that fixed my problem. I think it should be mentioned in PostgreSQL tutorial (in Al Bundy's example). thanks again, Oleg "Ross J. Reedstrom" wrote: > 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