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)