Updates thru view
Hi,
Here's what I am trying to do.
create table shek_tab( id int , name char(20), age int DEFAULT 20 );
create view shek_view as select name, age from shek_tab where age = 24 ;
create rule rule_update AS on update to shek_view
where old.age = 24
do update shek_tab set age = new.age where old.age = 24 ;
create rule rule_update2 AS on update to shek_view do instead nothing;
insert into shek_tab values(1, 'Name1', 24);
insert into shek_tab values(2, 'Name2', 16);
insert into shek_tab values(3, 'Name3', 24);
insert into shek_tab values(4, 'Name4', 19);
insert into shek_tab values(5, 'Name5', 92);
insert into shek_tab values(6, 'Name6', 24);
When I execute an update on the view all the 6 rows are updated whereas only
3 satisfy the condition.
testdb=# update shek_view set age = 25 where age = 24;
UPDATE 6
^^^^^^^^^
Is there something wrong in the rules that I created?
Any help is appreciated.
Thanks
Rajshekar
"Rajshekar" <riyer@kc.rr.com> writes:
create rule rule_update AS on update to shek_view
where old.age = 24
do update shek_tab set age = new.age where old.age = 24 ;
Try
create rule rule_update AS on update to shek_view
where old.age = 24
do update shek_tab set age = new.age where age = old.age;
Your form of the rule reduces to
update shek_tab set age = new.age where true;
which unsurprisingly updates all rows of the table.
I do not exactly see the point of the conditional rule, either.
Why not just
create rule rule_update AS on update to shek_view
do update shek_tab set age = new.age where age = old.age;
The view already restricts visibility of rows, you do not need to
do it twice more in the rules.
regards, tom lane