Re: Re: Updating views

On Tue, 5 Jun 2001, Rasmus Resen Amossen wrote:

> >  Problem is not 'where'. Views in Postgresql doesn't allows you insert,
> >  update or delete unless you define especila rules that explain Postgresql
> >  what to do in each case.
> >  Look Postgresql programming manual. You can see a few examples of rules in
> >  views.
>
> OK, but I can't see how to make a single rule that allows me to update
> an arbitray set of attributes from an arbitray where-clause.
>
> Example:
> I have a table named 'extable(a,b,c,d)' and a view 'exview(b,c,d)' for
> this table. How can I with a single rule allow the following updates:
>   update exview set b=10, c=0 where d=11;
>   update exview set b=0 where c > d;
>   update exview set d=123 where b=c and c=d;
>
> In other words: I want to make the update of 'exview' transparent to
> 'extable'.


It depends on your table and view defs too...

Given:
create table b1 (a int, b int);
create view v1 as select a from b1 where b>5;

create rule rr as on update to v1 do instead
 update b1 set a=NEW.a where a=OLD.a and b>5;

insert into b1 values (6, 6);
insert into b1 values (6, 7);
insert into b1 values (6, 8);
insert into b1 values (6, 4);
insert into b1 values (7, 4);
insert into b1 values (7, 7);
insert into b1 values (5, 100);

You can get stuff like:
sszabo=# select * from v1;
 a
---
 6
 6
 6
 7
 5
(5 rows)

sszabo=# select * from b1;
 a |  b
---+-----
 6 |   6
 6 |   7
 6 |   8
 6 |   4
 7 |   4
 7 |   7
 5 | 100
(7 rows)

sszabo=# update v1 set a=100 where a>5;
UPDATE 4
sszabo=# select * from v1;
  a
-----
   5
 100
 100
 100
 100
(5 rows)

sszabo=# select * from b1;
  a  |  b
-----+-----
   6 |   4
   7 |   4
   5 | 100
 100 |   6
 100 |   7
 100 |   8
 100 |   7
(7 rows)