Re: Pet Peeves

> rules are very very very very rarely useful.

I wouldn't say that. There are many use cases where rules are
just the thing. Plus they have an added performance benefit
when dealing with multiple rows in a single statement.

> yes, in general - I wouldn't mind to see postgresql implement fully
> updatable views.
> There's being a very long discussion about that on -hackers, and patch
> was even in cvs-head for a bit, but got dropped.
> probably enabling triggers for views would be the only way to do it, me thinks.
> I don't know how oracle guys got around it.

The Oracle solution is quite useful in a large set of cases. The
basic idea is this:

Since a view is arbitrarily complex, there is no way, in general,
that the database can know how to update it. Therefore the concept
of BEFORE or AFTER triggers doesn't really make sense (before or
after something the database can't do anyway).

So instead, the only kind of trigger they allow on a view is an
"INSTEAD OF" row-level trigger. The contract of the trigger function
is that it will be invoked once for each matching row in the view,
and the database will assume that the trigger will do the necessary
work to update that row. Thus Oracle assumes that the number of rows
updated matches the number of times that it invoked the trigger
function.

Apart from this last part, this is like defining a rule

CREATE RULE my_rule
AS ON INSERT/UPDATE/DELETE TO my_view
DO INSTEAD SELECT my_fn(old.*, new.*);

Of course the problem with using a rule in this way is that the
query is rewritten as a SELECT, and the client is told that no
rows were updated. This is where the INSTEAD OF trigger comes in
handy.

Dean.

_________________________________________________________________

Hotmail, Messenger, Photos and more - all with the new Windows Live. Get started!
http://www.download.live.com/