Re: Updateable views...

Eric D Nielsen <nielsene(at)MIT(dot)EDU> writes:

> I beleive this should allow queries such as:
> UPDATE (SELECT bar, baz FROM foo) SET bar=1 WHERE baz=2;
> as well as the
> CREATE VIEW foo_view AS SELECT bar, baz FROM foo;
> UPDATE foo_view SET bar=1 WHERE baz==2;
> DROP VIEW foo_view;
> three-query analog.
>
> However the one-query version can't be handled by the auto-
> generated ON UPDATE/DELETE/INSERT rules for views that I'm looking at.

Well, if you don't support joins or complex expressions then there's really no
value in inline views in update statements. WHERE clauses and excluded columns
are only really useful for security restrictions in real views.

It does seem to me that allowing complex expressions is fairly
straightforward: you bar updates to those columns, but allow use of them in
the rhs of set clauses.

That makes things like this possible:

CREATE VIEW foo as (select col, func1(col) as new_val where func2(col))

UPDATE foo SET col = new_val

which should be translated to:

UPDATE foo SET col = func1(col) WHERE func2(col)

That's not terribly useful in itself, but it means if you need those
additional columns for some other purpose, then you still get to take
advantage of the updateableness of the other columns.

I still hold out hope for eventually supporting joins, but that's obviously
more complicated to implement.

--
greg