Re: Thoughts about updateable views

Bernd Helmle <mailings(at)oopsware(dot)de> writes:
> Currently no SQL spec handy (i will have one soon) , i took a look onto
> O'Reillys "SQL in a
> Nutshell", where the WITH CHECK OPTION is defined as follows (translated
> from German....):

> Only data that can be read from the specific view can be updated, fields
> that aren't
> part of the view can't be updated. E.g. if a view is defined to display
> only the monthly
> salary of an employee, it wouldn't be possible to modify an employees
> hourly salary.

That sounds bogus to me. It's obvious that columns not present in the
view can't be updated through the view --- you simply do not have a way
to name them, so how could you affect them?

What the spec actually says, if I'm reading it correctly, is that CHECK
OPTION forbids you from using the view to insert/update *rows* that
would not appear in the view. For example given

CREATE VIEW v AS SELECT * FROM t WHERE flag != 42
WITH CHECK OPTION;

you could see the "flag" column in the view, and could set it on insert
or update --- so long as you didn't try to set it to 42. That would
mean that the result row was invisible in the view, which is what CHECK
OPTION forbids.

In the general case with complicated WHERE conditions, it seems this
would be extremely expensive to enforce. It would certainly be very
difficult to do it using only Postgres RULE mechanisms. So I'd suggest
not implementing the WITH CHECK OPTION feature; certainly not as part of
your first cut.

(But: it looks to me like the spec gives license to be restrictive about
the form of WHERE clauses in updatable views, so it might be that
something could be done about WITH CHECK OPTION with less pain than I'm
imagining.)

regards, tom lane