From: Kyle Bateman <kyle@actarg.com>
To: pgsql-sql@postgresql.org
Subject: question on update/delete rules on views
Date: Tue, 16 May 2000 18:17:10 -0600
Message-ID: <3921E506.82424CAF@actarg.com> (raw)
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?
Attachments:kyle.vcf (text/x-vcard, 290B) download
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: kyle@actarg.com
Subject: Re: question on update/delete rules on views
In-Reply-To: <3921E506.82424CAF@actarg.com>
References: <3921E506.82424CAF@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox