From kyle@actarg.com Wed May 17 00:17:09 2000 Received: from www.actarg.com (root@[209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA89592 for ; Tue, 16 May 2000 20:17:09 -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 RAA19845 for ; Tue, 16 May 2000 17:18:22 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by actarg.com (8.9.3/8.9.3) with ESMTP id SAA10269 for ; Tue, 16 May 2000 18:17:10 -0600 Message-ID: <3921E506.82424CAF@actarg.com> Date: Tue, 16 May 2000 18:17:10 -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: pgsql-sql@postgresql.org Subject: question on update/delete rules on views Content-Type: multipart/mixed; boundary="------------88932855983089994E411165" X-Archive-Number: 200005/21 This is a multi-part message in MIME format. --------------88932855983089994E411165 Content-Type: multipart/alternative; boundary="------------7741FF69F35A15C1232692F5" --------------7741FF69F35A15C1232692F5 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I am trying to create multiple views of a single table so different groups of people have access to different subsets of records within the table. So I need a complete set of rules for each view. My rules for select and insert seem to work just fine, but the update/delete rules hit all the records instead of being limited by the "where" clause in my calling query. I'm not sure if I'm doing something wrong or if I've found a bug. Any help would be greatly appreciated. Here is a script that demonstrates the problem: --Create our table drop table a; create table a ( one char(2), two char(2), three int4, primary key (one,two) ); --Insert some data to work with insert into a values ('aa','xz', 10); insert into a values ('ab','xz', 12); insert into a values ('ac','xz', 20); insert into a values ('ad','xz', 11); insert into a values ('ae','xz', 15); insert into a values ('ai','xz', 30); --Now view the data select * from a; --Now this view should have a valid rule for all operations drop view view_a; create view view_a as select one, two 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); --Test the insert rule 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 two = new.two; --Test the update rule update view_a set two = 'mn' where one = 'az'; --Notice all records got updated--not just the one where one = 'az' select * from a; According to the manual (chapter 42), when you have a view with no rule qualification but and instead clause, the resultant parsetree should include the rule action, plus the qualification from the original (calling) query. Doesn't this mean that my "where one = 'az'" clause should be appended to the update rule so that only one record gets updated? Or am I missing something? --------------7741FF69F35A15C1232692F5 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit  
I am trying to create multiple views of a single table so different groups of people have access to different subsets of records within the table.  So I need a complete set of rules for each view.  My rules for select and insert seem to work just fine, but the update/delete rules hit all the records instead of being limited by the "where" clause in my calling query.

I'm not sure if I'm doing something wrong or if I've found a bug.  Any help would be greatly appreciated.  Here is a script that demonstrates the problem:

--Create our table
drop table a;
create table a (
one char(2),
two char(2),
three int4,
primary key (one,two)
);

--Insert some data to work with
insert into a values ('aa','xz', 10);
insert into a values ('ab','xz', 12);
insert into a values ('ac','xz', 20);
insert into a values ('ad','xz', 11);
insert into a values ('ae','xz', 15);
insert into a values ('ai','xz', 30);

--Now view the data
select * from a;

--Now this view should have a valid rule for all operations
drop view view_a;
create view view_a as select one, two 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);

--Test the insert rule
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 two = new.two;

--Test the update rule
update view_a set two = 'mn' where one = 'az';

--Notice all records got updated--not just the one where one = 'az'
select * from a;

According to the manual (chapter 42), when you have a view with no rule qualification but and instead clause, the resultant parsetree should include the rule action, plus the qualification from the original (calling) query.  Doesn't this mean that my "where one = 'az'" clause should be appended to the update rule so that only one record gets updated?  Or am I missing something?
  --------------7741FF69F35A15C1232692F5-- --------------88932855983089994E411165 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 --------------88932855983089994E411165-- From brook@biology.nmsu.edu Wed May 17 13:52:10 2000 Received: from biology.nmsu.edu (biology.NMSU.Edu [128.123.5.72]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA67469 for ; Wed, 17 May 2000 09:52:10 -0400 (EDT) (envelope-from brook@biology.nmsu.edu) Received: (from brook@localhost) by biology.nmsu.edu (8.8.8/8.8.8) id HAA19867; Wed, 17 May 2000 07:52:02 -0600 (MDT) Date: Wed, 17 May 2000 07:52:02 -0600 (MDT) Message-Id: <200005171352.HAA19867@biology.nmsu.edu> X-Authentication-Warning: biology.nmsu.edu: brook set sender to brook@biology.nmsu.edu using -f From: Brook Milligan To: kyle@actarg.com CC: pgsql-sql@postgresql.org In-reply-to: <3921E506.82424CAF@actarg.com> (message from Kyle Bateman on Tue, 16 May 2000 18:17:10 -0600) Subject: Re: question on update/delete rules on views References: <3921E506.82424CAF@actarg.com> X-Archive-Number: 200005/25 create rule view_a_r_update as on update to view_a do instead update a set two = new.two; The problem is that your INSTEAD UPDATE rule is not constrained in any way; it DOES hit every row. Instead, do something like: 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. Cheers, Brook From kyle@actarg.com Wed May 17 15:15:47 2000 Received: from www.actarg.com (root@[209.180.91.25]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA30309 for ; Wed, 17 May 2000 11:15:47 -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 IAA23129; Wed, 17 May 2000 08:17:01 -0600 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by actarg.com (8.9.3/8.9.3) with ESMTP id JAA11735; Wed, 17 May 2000 09:15:46 -0600 Message-ID: <3922B7A2.5BEF5F5E@actarg.com> Date: Wed, 17 May 2000 09:15:46 -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> Content-Type: multipart/mixed; boundary="------------A56E96F8EBB5280491C46641" X-Archive-Number: 200005/27 This is a multi-part message in MIME format. --------------A56E96F8EBB5280491C46641 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; > > The problem is that your INSTEAD UPDATE rule is not constrained in any > way; it DOES hit every row. Instead, do something like: > > 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. > Thanks for the help. The problem with your suggestion is the view has to anticipate which column(s) the calling query wants to look at. What if the calling query has not specified the primary key in its where clause? In our real case, the table has many columns. There are a variety of queries that act on the table based on a variety of conditions in a variety of columns. I'd like to avoid having to have a separate rule or view for every possible where combination. Maybe that is not possible, but the manual seems to say it should work, so that's why I'm asking the question. --------------A56E96F8EBB5280491C46641 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 --------------A56E96F8EBB5280491C46641-- From brook@biology.nmsu.edu Wed May 17 17:51:51 2000 Received: from biology.nmsu.edu (biology.NMSU.Edu [128.123.5.72]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA30320 for ; Wed, 17 May 2000 13:51:51 -0400 (EDT) (envelope-from brook@biology.nmsu.edu) Received: (from brook@localhost) by biology.nmsu.edu (8.8.8/8.8.8) id LAA20341; Wed, 17 May 2000 11:51:48 -0600 (MDT) Date: Wed, 17 May 2000 11:51:48 -0600 (MDT) Message-Id: <200005171751.LAA20341@biology.nmsu.edu> X-Authentication-Warning: biology.nmsu.edu: brook set sender to brook@biology.nmsu.edu using -f From: Brook Milligan To: kyle@actarg.com CC: pgsql-sql@postgresql.org In-reply-to: <3922B7A2.5BEF5F5E@actarg.com> (message from Kyle Bateman on Wed, 17 May 2000 09:15:46 -0600) Subject: Re: question on update/delete rules on views References: <3921E506.82424CAF@actarg.com> <200005171352.HAA19867@biology.nmsu.edu> <3922B7A2.5BEF5F5E@actarg.com> X-Archive-Number: 200005/30 > 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. Thanks for the help. The problem with your suggestion is the view has to anticipate which column(s) the calling query wants to look at. What if the calling query has not specified the primary key in its where clause? In our real case, the table has many columns. There are a variety of queries that act on the table based on a variety of conditions in a variety of columns. I'd like to avoid having to have a separate rule or view for every possible where combination. Maybe that is not possible, but the manual seems to say it should work, so that's why I'm asking the question. 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). Cheers, Brook From dev@archonet.com Wed May 17 19:48:52 2000 Received: from www-uk-05.digiserve.com ([195.172.23.32]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA56272 for ; Wed, 17 May 2000 15:48:52 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id UAA15625; Wed, 17 May 2000 20:48:19 +0100 (BST) Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 970402A0FB; Wed, 17 May 2000 20:45:37 +0100 (BST) Message-ID: <007f01bfc038$7842ab80$1001a8c0@archonet.com> From: "Richard Huxton" To: "Kyle Bateman" Cc: References: <3921E506.82424CAF@actarg.com> <200005171352.HAA19867@biology.nmsu.edu> <3922B7A2.5BEF5F5E@actarg.com> Subject: Re: question on update/delete rules on views Date: Wed, 17 May 2000 20:45:32 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 X-Archive-Number: 200005/35 ----- Original Message ----- From: Kyle Bateman To: Brook Milligan Cc: Sent: Wednesday, May 17, 2000 4:15 PM Subject: Re: [SQL] question on update/delete rules on views > Brook Milligan wrote: > > > create rule view_a_r_update as on update to view_a > > do instead > > update a set two = new.two; > > > > The problem is that your INSTEAD UPDATE rule is not constrained in any > > way; it DOES hit every row. Instead, do something like: > > > > 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. > > > > Thanks for the help. The problem with your suggestion is the view has to > anticipate which column(s) the calling query wants to look at. What if > the calling query has not specified the primary key in its where clause? > In our real case, the table has many columns. There are a variety of > queries that act on the table based on a variety of conditions in a > variety of columns. I'd like to avoid having to have a separate rule or > view for every possible where combination. Maybe that is not possible, > but the manual seems to say it should work, so that's why I'm asking the > question. > AFAIK it doesn't matter if the original query used a field - the "old" and "new" in the rule represent the row being updated (before and after) - you can access any column. -- Richard Huxton From kyle@actarg.com Wed May 17 20:47:03 2000 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-- From brook@biology.nmsu.edu Wed May 17 21:01:36 2000 Received: from biology.nmsu.edu (biology.NMSU.Edu [128.123.5.72]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA78337 for ; Wed, 17 May 2000 17:01:36 -0400 (EDT) (envelope-from brook@biology.nmsu.edu) Received: (from brook@localhost) by biology.nmsu.edu (8.8.8/8.8.8) id PAA21328; Wed, 17 May 2000 15:01:35 -0600 (MDT) Date: Wed, 17 May 2000 15:01:35 -0600 (MDT) Message-Id: <200005172101.PAA21328@biology.nmsu.edu> X-Authentication-Warning: biology.nmsu.edu: brook set sender to brook@biology.nmsu.edu using -f From: Brook Milligan To: kyle@actarg.com CC: pgsql-sql@postgresql.org In-reply-to: <39230546.62BE1AD9@actarg.com> (message from Kyle Bateman on Wed, 17 May 2000 14:47:02 -0600) 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> <39230546.62BE1AD9@actarg.com> X-Archive-Number: 200005/37 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"? I don't know what you mean by these two different where conditions. To my understanding the only instance of a where condition in the rule is in the "do" part. Think of triggering the "do" part individually for each view tuple to be updated. If you wish to act on a table, you must identify how the parts of the current tuple identify a tuple or tuples in the target table. That is the sole role of the "where" condition in the "do" part of the rule. Cheers, Brook From tgl@sss.pgh.pa.us Wed May 17 21:47:40 2000 Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA54509 for ; Wed, 17 May 2000 17:47:40 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id RAA18956; Wed, 17 May 2000 17:47:13 -0400 (EDT) To: Kyle Bateman cc: Brook Milligan , pgsql-sql@postgresql.org Subject: Re: question on update/delete rules on views In-reply-to: <39230546.62BE1AD9@actarg.com> References: <3921E506.82424CAF@actarg.com> <200005171352.HAA19867@biology.nmsu.edu> <3922B7A2.5BEF5F5E@actarg.com> <200005171751.LAA20341@biology.nmsu.edu> <39230546.62BE1AD9@actarg.com> Comments: In-reply-to Kyle Bateman message dated "Wed, 17 May 2000 14:47:02 -0600" Date: Wed, 17 May 2000 17:47:13 -0400 Message-ID: <18953.958600033@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200005/38 Kyle Bateman writes: > 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"? The rule WHERE condition determines *whether* the rule is fired for update of a given view tuple. (If it isn't, then that tuple is processed as if there were no rule.) Assuming that the rule fires, a WHERE inside its action will determine which tuples it actually acts on. I think there are some examples in the docs of uses of nonempty rule WHERE conditions. If not, you could try looking at the rules regression test for examples ... regards, tom lane