Thread: Updating views
If I add a "where" clause on my views, I can't insert or update them anymore. Why? Example: CREATE TABLE temp (a int); CREATE VIEW tview AS SELECT a FROM temp WHERE a>10; INSERT INTO tview VALUES (13); ERROR: Cannot update a view without an appropriate rule What is the appropriate rule? -- Rasmus Resen Amossen | stud.mat.dat at the University of Copenhagen Jagtvej 120, -244 | http://www.math.ku.dk/muh 2200 Kbh. N | http://w1.1444.telia.com/~u144400001
On Mon, Jun 04, 2001 at 11:12:56PM +0200, Rasmus Resen Amossen wrote:
> If I add a "where" clause on my views, I can't insert or update them
> anymore. Why?
>
> Example:
> CREATE TABLE temp (a int);
> CREATE VIEW tview AS SELECT a FROM temp WHERE a>10;
>
> INSERT INTO tview VALUES (13);
> ERROR: Cannot update a view without an appropriate rule
>
> What is the appropriate rule?
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.
Greets.
David
> 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'. -- Rasmus Resen Amossen | stud.mat.dat at the University of Copenhagen Jagtvej 120, -244 | http://www.math.ku.dk/muh 2200 Kbh. N | http://w1.1444.telia.com/~u144400001
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)
Rasmus Resen Amossen <spunk@rhk.dk> writes:
> 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.
The reason the system doesn't do that for you is that it's *hard* to
figure out what to do for an arbitrary where-clause. An automatic rule
has no chance of doing the right thing, because the right thing depends
on what you intend. For example, if your view has
select ... where a>5;
what do you think ought to happen if someone tries to insert a row
with a<5? Is that an error? A no-op? Does the row go in anyway,
you just can't see it in the view? Does the row go into some other
table instead? Is it OK to change the A column at all? It all depends
on the semantics of your database design. So you have to figure out
what you want and write rules that do it.
The mechanics of the rule are not that painful once you've decided what
the reverse mapping from inserted/updated data to underlying tables
ought to be. One thing that may help is to realize that you don't need
a separate rule for each combination of set of attributes that might be
updated. "new.*" is defined for all columns including the ones that
didn't change, so you can just do something like
update ... set f1 = new.f1, f2 = new.f2, ...
without worrying about just which columns the user tried to update.
Likewise, the where clause in the user's query is not yours to worry
about; that condition gets added onto the stuff in your rule.
> In other words: I want to make the update of 'exview' transparent to
> 'extable'.
If it's really transparent, one wonders why you bothered with a view
at all. Useful views tend to be nontrivial mappings of the underlying
data, which is why it's nontrivial to figure out what the reverse
mapping ought to be.
regards, tom lane
Tom Lane writes: > For example, if your view has > select ... where a>5; > what do you think ought to happen if someone tries to insert a row > with a<5? It's an error if you specified WITH CHECK OPTION in the view definition, it succeeds otherwise. There's a standard semantic for it. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Tue, Jun 05, 2001 at 01:17:00AM +0200, 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;
something like...
create rule exmunge as
on update to exview
do instead (
update extable
set
-- stamp = current_timestamp,
-- something = old.fldA + new.fldB,
b = new.b,
c = new.c,
d = new.d
);
then, try
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;
is that what you're after?
--
#95: We are waking up and linking to each other. We are watching. But
we are not waiting. -- www.cluetrain.com
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!