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?