Received: from olympus.scw.org (209-9-156-98.sdsl.cais.net [209.9.156.98]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eB1L4IF22074; Fri, 1 Dec 2000 16:04:19 -0500 (EST) (envelope-from jburton@scw.org) Received: from hades (eos.scw.org [209.9.156.112]) by olympus.scw.org (8.10.1/8.10.1) with ESMTP id eB1L3kg27535; Fri, 1 Dec 2000 16:03:47 -0500 From: "Joel Burton" Organization: Support Center of Washington To: Tom Lane Date: Fri, 1 Dec 2000 16:03:46 -0500 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: Re: Rules with Conditions: Bug, or Misunderstanding CC: pgsql-hackers@postgresql.org, pgsql-sql@postgresql.org Message-ID: <3A27CBE2.20547.491631@localhost> References: <3A255246.21430.688927F3@localhost> In-reply-to: <19579.975544973@sss.pgh.pa.us> X-mailer: Pegasus Mail for Win32 (v3.12c) X-Archive-Number: 200012/6 X-Sequence-Number: 337 On 29 Nov 2000, at 19:42, Tom Lane wrote: > "Joel Burton" writes: > > create rule dev_ins as on update to dev_col_comments where > > old.description isnull do instead insert into pg_description ( > > objoid, description) values (old.att_oid, new.description); > > > create rule dev_upd as on update to dev_col_comments where > > old.description notnull do instead update pg_description set > > description=new.description where objoid=old.att_oid; > > > This doesn't work: I get a "cannot update view w/o rule" error > > message, both for fields where description was null, and for fields > > where it wasn't null. > > [... ] I think this will work: > > create rule dev_upd as on update to dev_col_comments do instead > ( > insert into pg_description (objoid, description) > select old.att_oid, new.description WHERE old.description isnull; > update pg_description set description=new.description > where objoid = old.att_oid; > ) Tom -- Thanks for the help. I had assumed (wrongly) that one could have conditional rules, and only if all the conditions fail, that it would go to the "cannot update view" end, and didn't realize that there *had* to be a single do instead. In any event, though, the rule above crashes my backend, as do simpler versions I wrote that try your CREATE RULE DO INSTEAD ( INSERT; UPDATE; ) idea. What information can I provide to the list to troubleshoot this? Thanks! -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)