CREATE VIEW defines a non-materialized view, which simply provides an alias
for the embedded
|TEMP / TEMPORARY||Mark the view as temporary.|
|OR REPLACE||If a view exists with the same name, replace it with the view defined in this statement. You cannot replace views that other views depend on, nor can you replace a non-view object with a view.|
|IF NOT EXISTS||If specified, do not generate an error if a view of the same name already exists.
If not specified, throw an error if a view of the same name already exists. (Default)
|view_name||A name for the view.|
|( col_ident… )||Rename the
TEMPORARY keyword creates a temporary view. Temporary views are
automatically dropped at the end of the SQL session and are not visible to other
connections. They are always created in the special
Temporary views may depend upon other temporary database objects, but non-temporary views may not depend on temporary objects.
Creating a view
CREATE VIEW purchase_sum_by_region AS SELECT sum(purchase.amount) AS region_sum, region.id AS region_id FROM region INNER JOIN user ON region.id = user.region_id INNER JOIN purchase ON purchase.user_id = user.id GROUP BY region.id;
The privileges required to execute this statement are:
- Ownership of existing
OR REPLACEis specified.
CREATEprivileges on the containing schema.
USAGEprivileges on all types used in the view definition.
USAGEprivileges on the schemas that all types in the statement are contained in.