pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joel Burton <jburton@scw.org>
Cc: pgsql-hackers@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Rules with Conditions: Bug, or Misunderstanding
Date: Wed, 29 Nov 2000 19:42:53 -0500
Message-ID: <19579.975544973@sss.pgh.pa.us> (raw)
In-Reply-To: <3A255246.21430.688927F3@localhost>
References: <3A255246.21430.688927F3@localhost>

"Joel Burton" <jburton@scw.org> 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



view thread (11+ messages)  latest in thread

Message-ID: <19579.975544973@sss.pgh.pa.us>
Permalink:  ../19579.975544973@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/19579.975544973@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jburton@scw.org, pgsql-hackers@postgresql.org
  Subject: Re: [SQL] Rules with Conditions: Bug, or Misunderstanding
  In-Reply-To: <19579.975544973@sss.pgh.pa.us>
  References: <3A255246.21430.688927F3@localhost> <19579.975544973@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox