question on update/delete rules on views

From: Kyle Bateman <kyle(at)actarg(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: question on update/delete rules on views
Date: 2000-05-17 00:17:10
Message-ID: 3921E506.82424CAF@actarg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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?

Attachment Content-Type Size
kyle.vcf text/x-vcard 291 bytes

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas Lockhart 2000-05-17 05:38:58 Re: Question about databases in alternate locations...
Previous Message Tom Lane 2000-05-16 19:11:56 Re: Index not used in functions in 7.0?