Skip to main content

CREATE VIEW

Creates a view.

Syntax​

CREATE [OR REPLACE] [RECURSIVE] VIEW <name> [ ( <column_name> [, ...] ) ]
    [ WITH ( <view_option_name> [= <view_option_value> ] [, ... ] ) ]
    AS <query>
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

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, 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 may not 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​

  • RECURSIVE

    Creates a recursive view. The syntax:

    CREATE RECURSIVE VIEW [ <schema> . ] <view_name> (<column_names>) AS SELECT <...>;

    is equivalent to

    CREATE VIEW [ <schema> . ] <view_name> AS WITH RECURSIVE <view_name> (<column_names>) AS (SELECT <...>) SELECT <column_names> FROM <view_name>;

    A view column name list must be specified for a recursive view.

  • <name>

    The name of a view to be created. You can prefix the name with a schema name to create the view in the specified schema. Otherwise, the view will be created in the current schema.

  • <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.

  • WITH ( <view_option_name> [= <view_option_value>] [, ... ] )

    This clause specifies optional parameters for a view; the following parameters are supported:

    • <check_option> (string)

      This parameter may be either local or cascaded, and is equivalent to specifying WITH [ CASCADED | LOCAL ] CHECK OPTION (see below). This option can be changed on existing views using ALTER VIEW.

    • <security_barrier> (boolean)

      Specify this parameter if the view is intended to provide row-level security.

  • <query>

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

Examples​

Create a view consisting of all products delivered in 2022:

CREATE VIEW sales_2022 AS
SELECT * 
FROM sales 
WHERE year = 2022;
note

Columns added to the table later than the command execution will not be included in the view.

Create a view that gets the top ten ranked baby names:

CREATE VIEW topten AS 
SELECT name, rank, gender, year 
FROM names, rank 
WHERE rank < '11' AND names.id=rank.id;

Create a recursive view consisting of the numbers from 1 to 100:

CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;
note

Although the name of the recursive view is schema-qualified in this CREATE VIEW command, its internal self-reference is not schema-qualified. This is because the name of the mplicitly-created common table expression (CTE) cannot be schema-qualified.

SQL standard compatibility​

The SQL standard specifies some additional capabilities for the CREATE VIEW statement that are not in Relyt. The optional clauses for the full SQL command in the standard are:

  • CHECK OPTION: This option has to do with updatable views. All INSERT and UPDATE commands on the view will be checked to ensure data satisfies 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 Relyt language extension. So is the concept of a temporary view.