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--