Is this correct behavior for ON DELETE rule?

From: "Rick Schumeyer" <rschumeyer(at)ieee(dot)org>
To: "'PgSql General'" <pgsql-general(at)postgresql(dot)org>
Subject: Is this correct behavior for ON DELETE rule?
Date: 2005-02-25 23:09:17
Message-ID: 000001c51b8f$0810f710$0200a8c0@dell8200
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Si Chen 2005-02-25 23:12:07 postgresql 8.0 on windows 2003 server
Previous Message josue 2005-02-25 22:44:58 row numbering