Re: Is this correct behavior for ON DELETE rule?

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Rick Schumeyer <rschumeyer(at)ieee(dot)org>
Cc: "'PgSql General'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Is this correct behavior for ON DELETE rule?
Date: 2005-02-25 23:22:56
Message-ID: 200502252322.j1PNMut15260@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2005-02-25 23:29:17 Re: Is this correct behavior for ON DELETE rule?
Previous Message Si Chen 2005-02-25 23:12:07 postgresql 8.0 on windows 2003 server