You use CREATE VIEW to create a view. A view is a table that is not permanently stored; its rows are derived only when needed.
The current authorization identifier must own the schema for which the view is created. It must have the SELECT privilege for the tables used and the EXECUTE privilege for the UDFs called.
CREATE VIEW table
{ [( column ,...)] AS query_expression [WITH CHECK OPTION] |
( column ,...) AS VALUES row ,... }
row ::= { ( expression ,...) | expression }
table
Name of the new view. The unqualified view name must be unique within the base tables and view names of the schema. You can qualify the view name with a database and schema name.
If you use the CREATE VIEW statement in a CREATE SCHEMA statement, you can qualify the view name only with the database and schema name from the CREATE SCHEMA statement.
(column,...)
Name of the columns of the view. If query_expression is specified, you only need to name the view columns if the column names of the tables resulting from query_expression are ambiguous or if there are some derived columns without a name.
(column,...) omitted:
The column names of the query_expression are used.
AS query_expression
Query expression that describes how the rows of the view are derived from existing base tables and views. The columns in the view have the same data type as the underlying columns in the query expression.
AS VALUES row ,...
The specified rows form the new view. All the rows must have the same number of columns, and corresponding columns must have compatible data types (see section "Compatibility between data types"). If several rows are specified, the data type of the view columns results from the rules described in section "Data type of the derived column for UNION".
expression
Each expression in row must be atomic. The row consists of the expression values in the order specified. A single expression therefore returns a row with one column.
Any tables named in query_expression and in row must belong to the same database as the view. You cannot include host variables and question marks as placeholders for unknown values in the query_expression and in row. If the columns in the view are named, the number of names must equal the number of columns in the expression or row table.
WITH CHECK OPTION
All rows that you insert or update via the view must satisfy all conditions of the query expression. The view must be updatable.
The query expression can only include multiple columns and UDFs in the SELECT clause, not in the WHERE clause.
WITH CHECK OPTION omitted:
If the view is updatable, you can insert or update rows in the view that do not satisfy the condition in the query expression. Such inserted or changed rows cannot subsequently be accessed via the view.
Privileges for the view
The current authorization identifier is granted the SELECT privilege for the view. This privilege includes GRANT authorization for granting this privilege to other users only if it possesses the SELECT privilege for all the tables used and the GRANT authorization identifier for the EXECUTE privilege for all UDFs called.
If the view is updatable, the current authorization identifier is granted the privileges INSERT, UPDATE, and DELETE on the view if it has been granted these privileges on the underlying base table. Each of these privileges includes the GRANT OPTION if and only if the corresponding privilege on the underlying base table includes the GRANT OPTION.
Updatable view
A view is updatable if query_expression is specified and the underlying query expression is updatable (see section "Updatability of query expressions").
Examples
Define a view which will contain all completed orders of the ORDERS base table.
CREATE VIEW completed
AS SELECT * FROM orders WHERE actual IS NOT NULL
The example defines the view SUMMARY which will contain the customer names and associated order numbers from the CUSTOMERS and ORDERS tables.
 |
|
The example defines the LOCALEDAYNAMES view, which contains the names of the days of the week and allocates each day of the week a number.
CREATE VIEW localedaynames (num, name)
AS VALUES (1 , 'Monday')
,(2 , 'Tuesday')
,(3 , 'Wednesday')
,(4 , 'Thursday')
,(5 , 'Friday')
,(6 , 'Saturday')
,(7 , 'Sunday')
You can use this to select the name of the day of the week for a DAY_NUM column.
SELECT ..., (SELECT name FROM localedaynames WHERE num = day_num)
Compared to the version below this not only is shorter but also has another advantage: If you switch to another language, you only have to change one single view definition instead of several SELECT expressions.
SELECT ..., CASE day_num WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
WHEN 7 THEN 'Sunday'
END
This is, of course, also an advantage if LOCALEDAYNAMES were a base table with this content. In that case, however, each use would involve access to persistently stored data in a file. With the view, this type of access is not necessary (just as with the CASE expression).
The view VIEW1 selects from the ORDERS table all order numbers, customer numbers, target completion dates and order status numbers for which the target completion date lies before the specified date.
CREATE VIEW view1 AS SELECT order_num,cust_num,target,order_status
FROM orders WHERE target < DATE'2014-05-01'
A second view, VIEW2 is defined to reference VIEW1. This contains the order numbers, customer numbers, target completion dates and order status numbers for target completion dates later than the specified date:
CREATE VIEW view2 AS SELECT order_num,cust_num,target,order_status
FROM view1 WHERE target > DATE'2013-05-01'
VIEW2 produces the following derived table:
order_num | cust_num | target | order_status |
210 | 106 | 4/1/2014 | 3 |
211 | 106 | 4/1/2014 | 4 |
250 | 105 | 3/1/2014 | 2 |
A new row is to be added to VIEW2:
INSERT INTO view2 (order_num,cust_num,target,order_status)
VALUES (310,100,DATE '2014-06-01',5)
The new row is added, but cannot be seen either in VIEW1 or VIEW2. The row complies with the WHERE condition in the definition of VIEW2, but not with the WHERE condition in the definition of VIEW1. If we expand the definition of VIEW1 in VIEW2, we see:
CREATE VIEW view2 AS SELECT view1.order_num, view1.cust_num,view1.target,view1.order_status FROM (SELECT orders.order_num, orders.cust_num,orders. target,orders. order_status FROM orders WHERE orders.target < DATE '2014-05-01') AS view1 WHERE view1.target > DATE '2013-05-01'
This makes it clear that the WHERE condition in VIEW1 is “inherited” by the definition of VIEW2, with the result that the row added to the ORDERS table is not visible in VIEW2.
If WITH CHECK OPTION is added to the definition of VIEW2, the INSERT statement is rejected, since only those rows are accepted which fulfill the WHERE condition in VIEW1.
The INSERT statement is, however, also rejected if WITH CHECK OPTION is added to the definition of VIEW2 only. Although the row to be inserted fulfils the WHERE condition in the definition of VIEW2, the INSERT statement is nevertheless rejected since the row fails to fulfil the WHERE condition of VIEW1.
See also
CREATE SCHEMA, DROP VIEW