Re: Is this correct behavior for ON DELETE rule?

Uh, because of your REFERENCES clause you have to delete from 'item'
first, then 'book':

> -- delete to item and book instead of bookview
> create rule bookviewdel as on delete to bookview do instead (
>       delete from item where id=old.id;
>       delete from book where id=old.id;
> );

And your posting is double-spaces for some reason.

---------------------------------------------------------------------------

Rick Schumeyer wrote:
>
>
> I have two related tables, "item" and "book".  I have defined
>
> a view, "bookview" that contains fields from item and book.
>
> My goal was to have all inserts, updates, and deletes performed
>
> on bookview rather than on the tables directly.  I was able
>
> to do this with ON INSERT and ON UPDATE rules easily.
>
>
>
> I am having trouble with the ON DELETE rule.  When a row is
>
> deleted from bookview, the appropriate row should be deleted
>
> from item and from book.  The example below only deletes the
>
> row from book.
>
>
>
> Is this expected behavior, and if so, what is the right way
>
> to do this?  At the moment I have defined an ON DELETE rule
>
> on item which works.  But I would prefer if this could be
>
> done on the view.
>
>
>
> Thanks for any help.
>
>
>
> ----------------------------------------------------------
>
> drop table book cascade;
>
> drop table item cascade;
>
>
>
> -- "parent" table
>
> create table item
>
>       (id serial primary key,
>
>       type  varchar(8),
>
>       title varchar(20)
>
> );
>
>
>
> -- "child" table
>
> create table book
>
>       (id     integer references item primary key,
>
>       title varchar(20),
>
>       author  varchar(20)
>
> );
>
>
>
> -- combine stuff from item and book tables
>
> create view bookview as
>
>       select i.id, b.title, b.author from item i, book b
>
>       where i.id=b.id;
>
>
>
> -- insert to item and book instead of bookview
>
> create rule bookviewins as on insert to bookview do instead (
>
>       insert into item (type, title)
>
>       values ('book', new.title);
>
>       insert into book (id, title, author)
>
>       values (currval('item_id_seq'), new.title, new.author);
>
> );
>
>
>
> -- delete to item and book instead of bookview
>
> create rule bookviewdel as on delete to bookview do instead (
>
>       delete from book where id=old.id;
>
>       delete from item where id=old.id;
>
> );
>
>
>
> -- everyone has access to bookview
>
> grant all on bookview to public;
>
>
>
> insert into bookview (title, author) values ('Dune','Herbert');
>
> insert into bookview (title, author) values ('Hobbit','Tolkein');
>
>
>
> select * from bookview;
>
>
>
> delete from bookview where author='Tolkein';
>
> -- "DELETE 0"
>
>
>
> select * from bookview;
>
> -- looks correct
>
>
>
> select * from item;
>
> -- shows both books
>
>
>
> select * from book;
>
> -- looks correct
>
>
>
>
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073