Creates a view on one or more tables or views.
view-nameis specified in its unqualified form, the view will be created in the schema which has the same name as the current ident.
view-nameis specified in its fully qualified form (i.e.
schema-name.view-name) the view will be created in the named schema (in this case, the current ident must be the creator of the specified schema).
If a list of column names is given in parentheses before the
query-expression, the columns in the view are named in accordance with this list. There must be the same number of names in the column list as there are columns addressed by the
query-expression. The names must be unique within the list.
If the column name list is omitted, the columns in the view will be given the same names as they have in the source table(s) or view(s) addressed in the select-specification. The column names in the source must all be unique in the view being created. If this is not the case, an explicit column name list must be given. An explicit column name list must also be given if columns in the view are defined as expressions without correlation names.
A view is created in accordance with the specification in the
query-expression, see SELECT for more information on
WITH CHECK OPTION
WITH CHECK OPTIONindicates that any data inserted into the view by
UPDATEstatements will be checked for conformity with the definition of the view. Attempts to insert data which do not conform to the view definition will be rejected.
The optional keyword
CASCADEDcan be explicitly specified in the
WITH CHECK OPTIONclause to ensure that any data inserted into a view which is based on this view will be also be checked for conformity with the definition of this view.
Thus, if an
UPDATEin a view based on this one results in an attempt to insert data into this view which does conform to the view definition, the data change operation will be rejected.
CASCADEDis not specified, it is assumed by default (use of the keyword
CASCADEDis now permitted to allow for future extensions to the Mimer SQL syntax).
query-expression, see SELECT.
SELECTaccess to the tables or views from which the view is created, and
EXECUTEprivilege on routines and
USAGEprivilege on sequences and domains referenced.
The view name may not be the same as the name of any other table, view, index, constraint or synonym belonging to the same schema.
The creator of the view is always granted
SELECTaccess to the view. If the view is updatable, see below, any access the creator may hold on the underlying table or view at the time the new view is created is also granted on the new view. Access to the view is granted
WITH GRANT OPTIONonly if the corresponding access to all underlying tables, views, routines, sequences and domains are held
WITH GRANT OPTION.
UPDATEstatements can only be performed on data accessible from the view. Insertion of a new row assigns the default value or null value to columns in the base table excluded from the view, in accordance with the definition of the columns. Deletion of a row from a view removes the entire row from the underlying base table, including columns invisible from the view.
select-specificationdefining the view may not contain references to host variables.
WITH CHECK OPTIONclause is illegal if the view is not updatable. A result set is only updatable if all of the following conditions are true:
- the keyword
DISTINCTis not specified
FROMclause specifies exactly one table reference and that table reference refers either to a base table or an updatable view
GROUP BYclause is not included
HAVINGclause is not included.
- the result set is not the product of an explicit
- the keyword
UNIONis not included
- the keyword
EXCEPTis not included
- the keyword
INTERSECTIONis not included
A view will always be updatable if an
INSTEAD OFtrigger exists on the view, regardless of the conditions previously mentioned. If there is an
INSTEAD OFtrigger any possible with check option for the view is ignored. If all the
INSTEAD OFtriggers on the view are dropped, the view will revert to not updatable if one or more of those conditions are not true.
If an updatable view is based on other views, insert and update operations are checked against all view definitions for which
WITH CHECK OPTIONis specified. Thus if
view-2is defined with check option on
view-1, which in turn is defined with check option on a base table, no changes may be made in the base table through either
view-2which violate the definition of
ExampleCREATE VIEW mimer_store_book.details AS SELECT authors_list, product AS title, producer AS publisher, format, price, stock, reorder_level, extract_date(release_date) AS release_date, 'ISBN:99-999-9999-9' as ISBN, -- ***** -- *****'ISBN:' || mimer_store_book.format_isbn(isbn) AS isbn, ean_code, status, product_search AS title_search, product_details.item_id, category_id, product_id, display_order, image_id FROM product_details JOIN mimer_store_book.titles ON product_details.item_id = mimer_store_book.titles.item_id;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40