Create View

Valid in: SQL, ESQL

The Create View statement uses a select statement to define the contents of a virtual table. The view definition is stored in the system catalogs. When the view is used in an statement, the statement operates on the underlying tables. When a table or view used in the definition of a view is dropped, the view is also dropped.

Data can be selected from a view the same way data is selected from a base table. However, updates, inserts, and deletes on views are subject to several restrictions. Updates, inserts, and deletes are allowed only if the view meets all the following conditions:

• The view is based on a single updateable table or view.
• All columns see columns in the base table or view (no aggregate functions or derived columns are allowed).
• The select statement omits distinct, group by, union, and having clauses.

Inserts are not allowed if a mandatory (not null not default) column in a base table is omitted from the view.

Note: This statement has additional considerations when used in a distributed environment. For more information, see the Ingres Star User Guide.

Syntax

The Create View statement has the following format:

[EXEC SQL] CREATE VIEW view_name
               [(column_name {, column_name})]
               AS select_stmt
              [WITH CHECK OPTION]

• view_name

  Defines the name of the view. It must be a valid object name.

• select_stmt

  Is a SELECT statement, as described in the Select statement description in this chapter.

• WITH CHECK OPTION

  Prevents an INSERT or UPDATE to a view that creates a row that does not comply with the view definition.

With Check Option Clause

The WITH CHECK OPTION clause prevents you from executing an insert or update to a view that creates a row that does not comply with the view definition (the qualification specified in the WHERE clause).

For example, if the following view is defined WITH CHECK OPTION:

create view myview
    as select *
    from mytable
    where mycolumn = 10
    with check option;

And the following update is attempted:

update myview set mycolumn = 5;

The update to the mycolumn column is rolled back, because the updated rows fail the mycolumn = 10 qualification specified in the view definition. If the WITH CHECK OPTION is omitted, any row in the view can be updated, even if the update results in a row that is no longer a part of the view.

The WITH CHECK OPTION is valid only for updateable views. The WITH CHECK OPTION clause cannot be specified if the underlying base table is used in a subselect in the SELECT statement that defines the view. You cannot update or insert into a view defined on top of a view specified with WITH CHECK OPTION if the resulting rows violate the qualification of the underlying view.

Embedded Usage

In an embedded program, constant expressions can be expressed in the select_stmt with host language string variables. If the select_stmt includes a where clause, use a host language string variable to specify the entire where clause qualification. Specify the with clause using a host string variable (with :hostvar).

Permissions

You must have all privileges required to execute the select statements that define the view.

Locking

The Create View statement requires an exclusive lock on the view's base tables.

Related Statements

Drop

Insert

Select (interactive)

Examples: Create View

The following are Create View statement examples:

1. Define a view of employee data including names, salaries, and name of the manager.

   create view empdpt (ename, sal, dname)
       as select employee.name, employee.salary,
       dept.name
       from employee, dept
       where employee.mgr = dept.mgr;

2. Define a view that uses aggregate functions to display the number of open orders and the average amount of the open orders for sales representative that has orders on file. This view is not updateable (because it uses aggregate functions).

   create view order_statistics
       (sales_rep, order_count, average_amt)
         as select salesrep, count(*), avg(ord_total)
         from open_orders
         group by sales_rep;

3. Define an updateable view showing the employees in the southern division. Specify check option to prevent any update that changes the region or adds an employee from another region.

   create view southern_emps
       as select * from employee
       where region = 'South'
       with check option;