CREATE VIEW
Go Up to Statement and Function Reference (Language Reference Guide)
Creates a new view of data from one or more tables. Available in gpre, DSQL, and isql.
CREATE VIEW name [(view_col [, view_col …])]
AS select [WITH CHECK OPTION];
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in
isql, the semicolon is a terminating symbol for the statement, so it must be included.| Argument | Description |
|---|---|
|
<name> |
Name for the view; must be unique among all view, table, and procedure names in the database. |
|
<view_col> |
Names the columns for the view:
|
|
<select> |
Specifies the selection criteria for rows to be included in the view. |
|
|
Prevents |
Description: CREATE VIEW describes a view of data based on one or more underlying tables in the database. The rows to return are defined by a SELECT statement that lists columns from the source tables. Only the view definition is stored in the database; a view does not directly represent physically stored data. It is possible to perform select, project, join, and union operations on views as if they were tables.
The user who creates a view is its owner and has all privileges for it, including the ability to GRANT privileges to other users, roles, triggers, views, and stored procedures. A user may have privileges to a view without having access to its base tables. When creating views:
- A read-only view requires
SELECTprivileges for any underlying tables. - An updatable view requires
ALLprivileges to the underlying tables.
The <view_col> option ensures that the view always contains the same columns and that the columns always have the same view-defined names.
View column names correspond in order and number to the columns listed in the SELECT clause, so specify all view column names or none.
A <view_col> definition can contain one or more columns based on an expression that combines the outcome of two columns. The expression must return a single value, and cannot return an array or array element. If the view includes an expression, the view-<column> option is required.
Any columns used in the value expression must exist before the expression can be defined.
A SELECT statement clause cannot include the ORDER BY clause.
When SELECT * is used rather than a column list, order of display is based on the order in which columns are stored in the base table.
WITH CHECK OPTION enables InterBase to verify that a row added to or updated in a view is able to be seen through the view before allowing the operation to succeed. Do not use WITH CHECK OPTION for read-only views.
You cannot select from a view that is based on the result set of a stored procedure.
An updatable view cannot have UNION clauses. To create such a view, use embedded SQL.
A view is updatable if:
- It is a subset of a single table or another updatable view.
- All base table columns excluded from the view definition allow
NULLvalues. - The
SELECTstatement of the view does not contain subqueries, aDISTINCTpredicate, aHAVINGclause, aggregate functions, joined tables, user-defined functions, or stored procedures.
If the view definition does not meet these conditions, it is considered read-only.
Read-only views can be updated by using a combination of user-defined referential constraints, triggers, and unique indexes.
Examples: The following isql statement creates an updatable view:
CREATE VIEW SNOW_LINE (CITY, STATE, SNOW_ALTITUDE) AS
SELECT CITY, STATE, ALTITUDE
FROM CITIES
WHERE ALTITUDE > 5000;
The next isql statement uses a nested query to create a view:
CREATE VIEW RECENT_CITIES AS
SELECT STATE, CITY, POPULATION
FROM CITIES WHERE STATE IN
(SELECT STATE FROM STATES WHERE STATEHOOD > '1-JAN-1850');
In an updatable view, the WITH CHECK OPTION prevents any inserts or updates through the view that do not satisfy the WHERE clause of the CREATE VIEW SELECT statement:
CREATE VIEW HALF_MILE_CITIES AS
SELECT CITY, STATE, ALTITUDE
FROM CITIES
WHERE ALTITUDE > 2500
WITH CHECK OPTION;
The WITH CHECK OPTION clause in the view would prevent the following insertion:
INSERT INTO HALF_MILE_CITIES (CITY, STATE, ALTITUDE)
VALUES ('Chicago', 'Illinois', 250);
On the other hand, the following UPDATE would be permitted:
INSERT INTO HALF_MILE_CITIES (CITY, STATE, ALTITUDE)
VALUES ('Truckee', 'California', 2736);
The WITH CHECK OPTION clause does not allow updates through the view which change the value of a row so that the view cannot retrieve it. For example, the WITH CHECK OPTION in the HALF_MILE_CITIES view prevents the following update:
UPDATE HALF_MILE_CITIES
SET ALTITUDE = 2000
WHERE STATE = 'NY';
The next isql statement creates a view that joins two tables, and so is read-only:
CREATE VIEW PHONE_LIST AS
SELECT EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO
FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE.DEPT_NO = DEPARTMENT.DEPT_NO;