I am currently thinking of updateable views for a possible student research
project. In this
case there comes some points to my mind, i want to share with the list.
a) Definition of an updateable view?
The first thing what i thought about was, what defines a updateable view.
An updateable
view cannot always be updateable, according to several RDBMS (SAPDB or DB2)
there
are the following issues:
- Multi-Join views without PKs of all underlying tables (so, how can the
base tables
adressed anyway?)
- Views that contains DISTINCT, Aggregates, GROUP BY, ORDER BY, HAVING or
functions
etc. in the col/table list
- Views that are based itself on views or on nested queries.
... [ room for enhancements.....]
b) The creation of an automatic INSERT/UPDATE or DELETE Rule had to be done
with
the creation of the SELECT Rule. I understand how PostgreSQL handles views
with its Rule
System, but what happens when no appropiate Rule can be created? Reject the
view make
it non-updateable per default or other action? In this case i don't
understand, if the WITH
CHECK OPTION is required for updateable views in PostgreSQL, since the view
rules can
be created as part of the SELECT rule and, according to the docs, the query
tree has
no entry for parts of the underlying table not mentioned in the views'
query.
This points are only a small overview what i have though about this
weekend. So, i believe
there are many more issues that should be mentioned when planning
updateable views,
aren't they? Some input would be nice, since i need a feeling for the
estimated complexity
of this project.
--
TIA
Bernd