CREATE
VIEW

Name

Synopsis

Description

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.

CREATE OR REPLACE VIEW is similar,
but if a view of the same name already exists, it is replaced.
The new query must generate the same columns that were generated
by the existing view query (that is, the same column names in the
same order and with the same data types), but it may add
additional columns to the end of the list. The calculations
giving rise to the output columns may be completely
different.

If a schema name is given (for example, CREATE VIEW myschema.myview ...) then the view is
created in the specified schema. Otherwise it is created in the
current schema. Temporary views exist in a special schema, so a
schema name cannot be given when creating a temporary view. The
name of the view must be distinct from the name of any other
view, table, sequence, or index in the same schema.

Parameters

TEMPORARY or TEMP

If specified, the view is created as a temporary view.
Temporary views are automatically dropped at the end of the
current session. Existing permanent relations with the same
name are not visible to the current session while the
temporary view exists, unless they are referenced with
schema-qualified names.

If any of the tables referenced by the view are
temporary, the view is created as a temporary view (whether
TEMPORARY is specified or
not).

name

The name (optionally schema-qualified) of a view to be
created.

column_name

An optional list of names to be used for columns of the
view. If not given, the column names are deduced from the
query.

query

A SELECT or VALUES command which will provide the
columns and rows of the view.

Notes

Currently, views are read only: the system will not allow an
insert, update, or delete on a view. You can get the effect of an
updatable view by creating rules that rewrite inserts, etc. on
the view into appropriate actions on other tables. For more
information see CREATE
RULE.

Be careful that the names and types of the view's columns will
be assigned the way you want. For example:

CREATE VIEW vista AS SELECT 'Hello World';

is bad form in two ways: the column name defaults to
?column?, and the column data type
defaults to unknown. If you want a string
literal in a view's result, use something like:

CREATE VIEW vista AS SELECT text 'Hello World' AS hello;

Access to tables referenced in the view is determined by
permissions of the view owner. In some cases, this can be used to
provide secure but restricted access to the underlying tables.
However, not all views are secure against tampering; see Section 37.4 for details. Functions
called in the view are treated the same as if they had been
called directly from the query using the view. Therefore the user
of a view must have permissions to call all functions used by the
view.

When CREATE OR REPLACE VIEW is used
on an existing view, only the view's defining SELECT rule is
changed. Other view properties, including ownership, permissions,
and non-SELECT rules, remain unchanged. You must own the view to
replace it (this includes being a member of the owning role).

Examples

Create a view consisting of all comedy films:

CREATE VIEW comedies AS
SELECT *
FROM films
WHERE kind = 'Comedy';

Compatibility

The SQL standard specifies some additional capabilities for
the CREATE VIEW statement:

This option has to do with updatable views. All
INSERT and UPDATE commands on the view will be checked
to ensure data satisfy the view-defining condition (that
is, the new data would be visible through the view). If
they do not, the update will be rejected.

LOCAL

Check for integrity on this view.

CASCADED

Check for integrity on this view and on any dependent
view. CASCADED is assumed if
neither CASCADED nor LOCAL is specified.

CREATE OR REPLACE VIEW is a
PostgreSQL language extension.
So is the concept of a temporary view.