Thread: 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