Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eAU0gvt80848; Wed, 29 Nov 2000 19:42:58 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id eAU0grY19582; Wed, 29 Nov 2000 19:42:53 -0500 (EST) To: "Joel Burton" cc: pgsql-hackers@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] Rules with Conditions: Bug, or Misunderstanding In-reply-to: <3A255246.21430.688927F3@localhost> References: <3A255246.21430.688927F3@localhost> Comments: In-reply-to "Joel Burton" message dated "Wed, 29 Nov 2000 19:00:22 -0500" Date: Wed, 29 Nov 2000 19:42:53 -0500 Message-ID: <19579.975544973@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200011/1314 X-Sequence-Number: 1413 "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. Hm. Perhaps the "cannot update view" test is too strict --- it's not bright enough to realize that the two rules together cover all cases, so it complains that you *might* be trying to update the view. As the code stands, you must provide an unconditional DO INSTEAD rule to implement insertion or update of a view. I'm not sure this is a big problem, though, because the solution is simple: provide an unconditional rule with multiple actions. For example, 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; ) but I haven't tried it... regards, tom lane