pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquestion on update/delete rules on views
8+ messages / 4 participants
[nested] [flat]
* question on update/delete rules on views
@ 2000-05-17 00:17 Kyle Bateman <kyle@actarg.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kyle Bateman @ 2000-05-17 00:17 UTC (permalink / raw)
To: 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?
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on update/delete rules on views
@ 2000-05-17 13:52 Brook Milligan <brook@biology.nmsu.edu>
parent: Kyle Bateman <kyle@actarg.com>
0 siblings, 1 reply; 8+ messages in thread
From: Brook Milligan @ 2000-05-17 13:52 UTC (permalink / raw)
To: kyle@actarg.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on update/delete rules on views
@ 2000-05-17 15:15 Kyle Bateman <kyle@actarg.com>
parent: Brook Milligan <brook@biology.nmsu.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Kyle Bateman @ 2000-05-17 15:15 UTC (permalink / raw)
To: Brook Milligan <brook@biology.nmsu.edu>; +Cc: pgsql-sql
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.
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on update/delete rules on views
@ 2000-05-17 17:51 Brook Milligan <brook@biology.nmsu.edu>
parent: Kyle Bateman <kyle@actarg.com>
1 sibling, 1 reply; 8+ messages in thread
From: Brook Milligan @ 2000-05-17 17:51 UTC (permalink / raw)
To: kyle@actarg.com; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on update/delete rules on views
@ 2000-05-17 19:45 Richard Huxton <dev@archonet.com>
parent: Kyle Bateman <kyle@actarg.com>
1 sibling, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2000-05-17 19:45 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: pgsql-sql
----- Original Message -----
From: Kyle Bateman <kyle@actarg.com>
To: Brook Milligan <brook@biology.nmsu.edu>
Cc: <pgsql-sql@postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on update/delete rules on views
@ 2000-05-17 20:47 Kyle Bateman <kyle@actarg.com>
parent: Brook Milligan <brook@biology.nmsu.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Kyle Bateman @ 2000-05-17 20:47 UTC (permalink / raw)
To: Brook Milligan <brook@biology.nmsu.edu>; +Cc: pgsql-sql
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;
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on update/delete rules on views
@ 2000-05-17 21:01 Brook Milligan <brook@biology.nmsu.edu>
parent: Kyle Bateman <kyle@actarg.com>
1 sibling, 0 replies; 8+ messages in thread
From: Brook Milligan @ 2000-05-17 21:01 UTC (permalink / raw)
To: kyle@actarg.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: question on update/delete rules on views
@ 2000-05-17 21:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kyle Bateman <kyle@actarg.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2000-05-17 21:47 UTC (permalink / raw)
To: Kyle Bateman <kyle@actarg.com>; +Cc: Brook Milligan <brook@biology.nmsu.edu>; pgsql-sql
Kyle Bateman <kyle@actarg.com> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2000-05-17 21:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-05-17 00:17 question on update/delete rules on views Kyle Bateman <kyle@actarg.com>
2000-05-17 13:52 ` Brook Milligan <brook@biology.nmsu.edu>
2000-05-17 15:15 ` Kyle Bateman <kyle@actarg.com>
2000-05-17 17:51 ` Brook Milligan <brook@biology.nmsu.edu>
2000-05-17 20:47 ` Kyle Bateman <kyle@actarg.com>
2000-05-17 21:01 ` Brook Milligan <brook@biology.nmsu.edu>
2000-05-17 21:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-05-17 19:45 ` Richard Huxton <dev@archonet.com>
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