Received: from www.actarg.com (root@[209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA53053 for ; Wed, 17 May 2000 16:47:03 -0400 (EDT) (envelope-from kyle@actarg.com) Received: from actarg.com (root@[192.168.2.16]) by www.actarg.com (8.8.7/8.8.7) with ESMTP id NAA24089; Wed, 17 May 2000 13:48:18 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by actarg.com (8.9.3/8.9.3) with ESMTP id OAA12153; Wed, 17 May 2000 14:47:02 -0600 Message-ID: <39230546.62BE1AD9@actarg.com> Date: Wed, 17 May 2000 14:47:02 -0600 From: Kyle Bateman Organization: Action Target Inc X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Brook Milligan CC: pgsql-sql@postgresql.org Subject: Re: question on update/delete rules on views References: <3921E506.82424CAF@actarg.com> <200005171352.HAA19867@biology.nmsu.edu> <3922B7A2.5BEF5F5E@actarg.com> <200005171751.LAA20341@biology.nmsu.edu> Content-Type: multipart/mixed; boundary="------------9E5428AABB9D097AE55C7A45" X-Archive-Number: 200005/36 This is a multi-part message in MIME format. --------------9E5428AABB9D097AE55C7A45 Content-Type: multipart/alternative; boundary="------------227ED117C969BC50CC0C3510" --------------227ED117C969BC50CC0C3510 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Brook Milligan wrote: > > create rule view_a_r_update as on update to view_a > > do instead > > update a set two = new.two > > where id = old.id; > > > > where id is a primary key in your table. > > I think you misunderstand what is going on. The original WHERE clause > (in your query) defines a set of tuples to which the UPDATE rule will > be applied. In the example above, each of those tuples will have a > primary key value (old.id in that case) and the matching field(s) in > table (or view) a will be changed as dictated by the rule. Thus, for > every tuple selected by your WHERE clause, the corresponding tuple in > the underlying table will be updated. Note that as many fields as you > wish to allow updates on can be included in the set ... part of the > rule; any that are not different will just be changed to the same > value (i.e., there will be no effect). Consequently, you don't need > lots of rules for every combination of columns (unless there are other > reasons to restrict the set of columns modifiable by different views). > Thanks Brook. That makes sense to me now. Here's the test file that demonstrates the proper (or at least a better) approach. I'll post the whole thing for the benefit of others (who wish to avoid looking as stupid as me). Even with all the examples of rules and views in the docs, I had a hard time finding an example of a view that simply gives fully functional access to a single underlying table. BTW, do you have an equally sensible explanation of how the "where condition" that is part of the rule syntax differs from the where clause that comes after the "do instead"? drop table a; create table a ( one char(2), two char(2), three int4, primary key (one,two) ); insert into a values ('aa','xz', 10); insert into a values ('ab','xz', 12); insert into a values ('ac','xz', 100); insert into a values ('ad','xz', 11); insert into a values ('ae','xz', 15); insert into a values ('ai','xz', 30); drop view view_a; create view view_a as select one, two, three from a; create rule view_a_r_insert as on insert to view_a do instead insert into a (one, two, three) values (new.one, new.two, 100); insert into view_a (one, two) values ('az','xy'); create rule view_a_r_update as on update to view_a do instead update a set one = new.one, two = new.two, three = new.three where one = old.one and two = old.two; select * from a; update view_a set two = 'mn' where one = 'az'; select * from a; update view_a set two = 'mo' where three = 100; select * from a; --------------227ED117C969BC50CC0C3510 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Brook Milligan wrote:
   >    create rule view_a_r_update as on update to view_a
   >            do instead
   >                    update a set two = new.two
   >                    where id = old.id;
   >
   > where id is a primary key in your table.

I think you misunderstand what is going on.  The original WHERE clause
(in your query) defines a set of tuples to which the UPDATE rule will
be applied.  In the example above, each of those tuples will have a
primary key value (old.id in that case) and the matching field(s) in
table (or view) a will be changed as dictated by the rule.  Thus, for
every tuple selected by your WHERE clause, the corresponding tuple in
the underlying table will be updated.  Note that as many fields as you
wish to allow updates on can be included in the set ... part of the
rule; any that are not different will just be changed to the same
value (i.e., there will be no effect).  Consequently, you don't need
lots of rules for every combination of columns (unless there are other
reasons to restrict the set of columns modifiable by different views).
 

Thanks Brook.  That makes sense to me now.  Here's the test file that demonstrates the proper (or at least a better) approach.  I'll post the whole thing for the benefit of others (who wish to avoid looking as stupid as me).  Even with all the examples of rules and views in the docs, I had a hard time finding an example of a view that simply gives fully functional access to a single underlying table.

BTW, do you have an equally sensible explanation of how the "where condition" that is part of the rule syntax differs from the where clause that comes after the "do instead"?

drop table a;

create table a (
one char(2),
two char(2),
three int4,
primary key (one,two)
);

insert into a values ('aa','xz', 10);
insert into a values ('ab','xz', 12);
insert into a values ('ac','xz', 100);
insert into a values ('ad','xz', 11);
insert into a values ('ae','xz', 15);
insert into a values ('ai','xz', 30);
 

drop view view_a;
create view view_a as select one, two, three from a;

create rule view_a_r_insert as on insert to view_a
    do instead
        insert into a (one, two, three)
        values (new.one, new.two, 100);
 

insert into view_a (one, two) values ('az','xy');

create rule view_a_r_update as on update to view_a
    do instead
        update a set one = new.one, two = new.two, three = new.three
        where one = old.one and two = old.two;

select * from a;
update view_a set two = 'mn' where one = 'az';
select * from a;
update view_a set two = 'mo' where three = 100;
select * from a;
 
  --------------227ED117C969BC50CC0C3510-- --------------9E5428AABB9D097AE55C7A45 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Bateman Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle tel;fax:801-377-8096 tel;work:801-377-8033x101 x-mozilla-html:FALSE url:www.actiontarget.com org:Action Target Inc adr:;;PO Box 636;Provo;UT;84603;US version:2.1 email;internet:kyle@actiontarget.com title:President x-mozilla-cpt:;-15520 fn:Kyle Bateman end:vcard --------------9E5428AABB9D097AE55C7A45--