CREATE VIEW

Use the CREATE VIEW command to create a view. A view is not physically materialized. Instead, the system automatically generates a query-rewrites retrieve rule to support retrieve operations on the view. Views are read-only. The system does not allow an insert, update, or delete on a view.

Syntax

Syntax for creating a view:

CREATE [OR REPLACE] VIEW <viewname> AS SELECT <query>

Inputs

The CREATE VIEW command takes the following inputs:

Table 1. CREATE VIEW inputs
Input	Description
OR REPLACE	If a view with the specified name does not already exist, a new view is created regardless of whether the OR REPLACE phrase is specified. However, if a view with the specified name already exists and the OR REPLACE phrase is: Specified, that view is overwritten. Not specified, an error message is issued and the view is not overwritten. Any granted privileges for the view are retained.
<viewname>	The name of the view to be created.
<query>	The SQL query that determines the columns and rows of the view. For more information, see SELECT (to retrieve rows).

Outputs

The CREATE VIEW command has the following outputs:

Table 2. CREATE VIEW outputs
Output	Description
`CREATE VIEW`	The view was successfully created.
`ERROR: Relation 'view' already exists`	A view with the specified name already exists in the database.

Privileges

You must be the admin user, the database or schema owner, or your account must have the Create View privileged. You must have Select privileges to the base table of the view. To replace a view, you must also have Alter privilege for theview or for the View object class.

Usage

The following provides sample usage:

Create a view that consists of all comedy films:

MYDB.SCH1(USER)=> CREATE VIEW kinds AS
       SELECT *
       FROM films
       WHERE kind = 'Comedy';

Display the view:

MYDB.SCH1(USER)=> SELECT * FROM kinds; 
   code  | title                     | did | date_prod  | kind    | len
   ------+---------------------------+-----+------------+---------+--
   C_701 | There's a Girl in my Soup | 107 | 1970-06-11 | Comedy  | 
01:36:00
   UA502 | Bananas                   | 105 | 1971-07-13 | Comedy  | 
01:22:00

Replace the view while retaining the privileges that were granted for the original view:

MYDB.SCH1(USER)=> CREATE OR REPLACE VIEW kinds AS
       SELECT *
       FROM films
       WHERE kind = 'Action';