CREATE VIEW — define a new view


CREATE [OR REPLACE] VIEW [schema.]view [ ( column_name [ ,column_name ]... ) ] AS SELECT query


CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query. The column_name list is optional. However, if the column_name list is provided, the number of columns in the column_name list must be identical to the number of columns in the SELECT query. If a schema name is specified (for example, CREATE VIEW myschema.myview ...) then the view is created in the specified schema; otherwise it is created in the current schema. The view name must be distinct from the name of any other view or table in the schema that the view is being created in. Use DROP VIEW to drop views (See DROP VIEW).

When the OR REPLACE clause is used for an existing view name, the effect is that of dropping the existing view and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a view with the specified name does not already exist.

A view cannot be created on a temporary table.

Note: When using CREATE OR REPLACE statements, and the same database object is created concurrently from two different transactions, the following error may be returned:

duplicate value in unique index ...

Note: In the event that a user-defined function is called by an expression-based index, any attempt to drop or modify the function will complete with an error warning but the index will not be recreated.

The CREATE VIEW command used to create each view is stored in SYSTEM.TABLES in the VIEWDEFINITION column. Any tables used in view definitions are stored in SYSTEM.VIEW_TABLES. See TABLES System Table Description, VIEW_TABLES System Table Description, and Example1 below.