Re: Re: Updating views

Rasmus Resen Amossen <spunk(at)rhk(dot)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