Re: Updateable views

> - What if we cannot create one of the three rules?
> Make the rule not updateable at all?
> Or create the rules we can? (i think this is the
> correct)

I seem to be in the minority here. But I think creating complex rules to
fiddle with the updates to translate them to the underlying tables is the
wrong approach.

I think you want to extend the SQL syntax to allow updating views, and
implement plan nodes and executor functionality to handle them. So things
like this works:

UPDATE (SELECT id,val FROM t) SET val=0 where id < 100

Then the rules you create on the views are just like the rules for SELECT,
they simply mechanically replace the view with the view definition.

I think this is the right approach because:

a) I think creating the general rules to transform an update into an update on
the underlying table will be extremely complex, and you'll only ever be
able to handle the simplest cases. By handling the view at planning time
you'll be able to handle arbitrarily complex cases limited only by whether
you can come up with reasonable semantics.

b) I think it's aesthetically weird to have functionality that's only
accessible via creating DDL objects and then using them, and not accessible
directly in a single SQL DML command. Ie, it would be strange to have to
create a "temporary view" just in order to execute an update because
there's no equivalent syntax available for use directly.

> General Restrictions!!!
> ---------------------------
> - The column target list holds column fields only,
> that are retrieved from one base relation / view
> only. (NO joined views).

I know there are other uses for updatable views (eg implementing column-based
security policies) but the _only_ reason I ever found them useful in Oracle
was precisely for joined views. They're the Oracle blessed method for
achieving the same performance win as Postgres's FROM clause.

So in Oracle you can do:

UPDATE (select a.val as newval, b.b_id, b.val from a,b where a.b_id = b.b_id) SET val = newval

--
greg