FirebirdSQL logo
 INDEXTRIGGER 

CREATE VIEW

Creates a view

Available in

DSQL

Syntax
CREATE VIEW viewname [<full_column_list>]
  AS <select_statement>
  [WITH CHECK OPTION]

<full_column_list> ::= (colname [, colname ...])
Table 1. CREATE VIEW Statement Parameters
Parameter Description

viewname

View name.The maximum length is 63 characters

select_statement

SELECT statement

full_column_list

The list of columns in the view

colname

View column name.Duplicate column names are not allowed.

The CREATE VIEW statement creates a new view.The identifier (name) of a view must be unique among the names of all views, tables, and stored procedures in the database.

The name of the new view can be followed by the list of column names that should be returned to the caller when the view is invoked.Names in the list do not have to be related to the names of the columns in the base tables from which they derive.

If the view column list is omitted, the system will use the column names and/or aliases from the SELECT statement.If duplicate names or non-aliased expression-derived columns make it impossible to obtain a valid list, creation of the view fails with an error.

The number of columns in the view’s list must match the number of columns in the selection list of the underlying SELECT statement in the view definition.

Note
Additional Points

  • If the full list of columns is specified, it makes no sense to specify aliases in the SELECT statement because the names in the column list will override them

  • The column list is optional if all the columns in the SELECT are explicitly named and are unique in the selection list

Updatable Views

A view can be updatable or read-only.If a view is updatable, the data retrieved when this view is called can be changed by the DML statements INSERT, UPDATE, DELETE, UPDATE OR INSERT or MERGE.Changes made in an updatable view are applied to the underlying table(s).

A read-only view can be made updatable with the use of triggers.Once triggers have been defined on a view, changes posted to it will never be written automatically to the underlying table, even if the view was updatable to begin with.It is the responsibility of the programmer to ensure that the triggers update (or delete from, or insert into) the base tables as needed.

A view will be automatically updatable if all the following conditions are met:

  • the SELECT statement queries only one table or one updatable view

  • the SELECT statement does not call any stored procedures

  • each base table (or base view) column not present in the view definition meets one of the following conditions:

    • it is nullable

    • it has a non-NULL default value

    • it has a trigger that supplies a permitted value

  • the SELECT statement contains no fields derived from subqueries or other expressions

  • the SELECT statement does not contain fields defined through aggregate functions (MIN, MAX, AVG, SUM, COUNT, LIST, etc.), statistical functions (CORR, COVAR_POP, COVAR_SAMP, etc.), linear regression functions (REGR_AVGX, REGR_AVGY, etc.) or any type of window function

  • the SELECT statement contains no ORDER BY, GROUP BY or HAVING clause

  • the SELECT statement does not include the keyword DISTINCT or row-restrictive keywords such as ROWS, FIRST, SKIP, OFFSET or FETCH

Note
The RETURNING clause and updatable views

The RETURNING clause of a DML statement used on a view made updatable using triggers may not always report the correct values.For example, values of identity column, computed columns, default values, or other expressions performed by the trigger will not be automatically reflected in the RETURNING columns.

To report the right values in RETURNING, the trigger will need to explicitly assign those values to the columns of the NEW record.