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@xxxxxxxxxxxxxxxx | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
