CREATE VIEW statement

Use the CREATE VIEW statement to create a new view that is based on one or more existing tables and views that reside in the database, or in another database of the local database server or of a different database server.

Syntax

(explicit id crv003) crv003 CREATE VIEW [IF NOT EXISTS] view [owner] [ { ( column ) | OF TYPE row_type } ] AS <Subset of SELECT Statement>[] [ WITH CHECK OPTION ]
Element Description Restrictions Syntax
column Name that you declare here for a column in view. Default is a column name from Projection list of SELECT. See Naming View Columns. Identifier
owner Owner of the view. If omitted, default is the user ID that issues the statement. To specify another user ID requires DBA access privilege. Owner name
row_type Named-row type for typed view Must already exist in the database Data Type
view Name that you declare here for the view Must be unique among view, table, sequence, and synonym names in the database. Identifier

Usage

A view is a virtual table, defined by a SELECT statement. Except for the statements in the following list, you can specify the name or synonym of a view in any SQL statement where the name of a table is syntactically valid:
  • ALTER FRAGMENT
  • CREATE INDEX
  • CREATE TABLE
  • CREATE TRIGGER
  • RENAME TABLE
  • START VIOLATIONS TABLE
  • STOP VIOLATIONS TABLE
  • TRUNCATE
  • UPDATE STATISTICS
You must specify the name of a view when you use the CREATE TRIGGER statement to define an INSTEAD OF trigger on a view, but the syntax and functionality are different from those of a trigger defined on a table.

Updating Through Views prohibits non-updatable views in INSERT, DELETE, or UPDATE statements (where other views are valid).

To create a view, you must have the Select privilege on all columns from which the view is derived. You can query a view as if it were a table, and in some cases, you can update it as if it were a table; but a view is not a table.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a view of the specified name is already registered in the current database, or if the specified name is the identifier of a table, synonym, or sequence object in the current database.

The view consists of the set of rows and columns that the SELECT statement in the view definition returns each time you refer to the view in a query.

In some cases, the database server merges the SELECT statement of the user with the SELECT statement defining the view and executes the combined statements. In other cases, a query against a view might execute more slowly than expected, if the complexity of the view definition causes the database server to create a temporary table (referred to as a materialized view). For more information on materialized views, see the Informix® Performance Guide.

The view reflects changes to the underlying tables, but with two exceptions:
  • If a SELECT * specification defines the view, the view has only the columns that existed in the underlying tables when the view was defined by CREATE VIEW. Any new columns that are subsequently added to the underlying tables with the ALTER TABLE statement do not appear in the view.
  • If a GRANT or REVOKE statement changes the discretionary access privileges on any table referenced in the view definition, the database server does not automatically apply those access privilege changes to the view.
To force modifications of the access privileges or schema of an underlying table to be applied to the view, you can use the DROP VIEW and CREATE VIEW statements of SQL to drop and recreate the view. You can also use the CREATE VIEW and CREATE TRIGGER statements to recreate, respectively, any dependent views or INSTEAD OF triggers that the DROP VIEW statement destroyed.

The view inherits the data types of the columns in the tables from which the view is derived. The database server determines data types of virtual columns from the nature of the expression.

The SELECT statement is stored in the sysviews system catalog table. When you subsequently refer to a view in another statement, the database server performs the defining SELECT statement while it executes the new statement.

In DB-Access, if you create a view outside the CREATE SCHEMA statement, you receive warnings if you use the -ansi flag or if you set the DBANSIWARN environment variable.

The following statement creates a view that is based on the person table. When you create a view like this, which has no OF TYPE clause, the view is referred to as an untyped view. 
CREATE VIEW v1 AS SELECT * FROM person;
1 See Subset of SELECT syntax valid in view definitions