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