CREATE VIEW

Define a new view.

Summary

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

describe

CREATE VIEW Defines the view of the query. This view is not objectified. Instead, run the query every time the view is referenced in the query. Note: If the view is continuously gathered, it will be materialized.

CREATE OR REPLACE VIEW is similar, but if a view with the same name already exists, it is replaced. A new query must generate the same column as the existing view query generates the column (i.e., the same column names are in the same order and have the same data type), but it may add other columns to the end of the list. The calculations that produce output columns may be completely different.

If a schema name is specified, a view is created in the specified schema. Otherwise, it will be created in the current mode. Temporary views exist in special schemas, so the schema name may not be given when creating a temporary view. The name of the view must be different from the name of any other view, table, sequence, index, or external table in the same schema.

Parameters

TEMPORARY | TEMP

If specified, the view is created as a temporary view. Temporary views are automatically deleted at the end of the current session. When a temporary view exists, the current session will not see the existing permanent tables with the same name unless they are referenced using a schema-qualified name. If any tables referenced by the view are temporary tables, the view will be created as a temporary view (with or without TEMPORARY specified).

RECURSIVE

Create a recursive view. Syntax:
CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...;
Equivalent to
CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;
The recursive view must be specified.

name

The name of the view to be created (can be specified by the schema).

column_name

An optional list of names for view columns. If not given, the column name is derived from the query.

WITH ( view_option_name [= view_option_value] [, ... ] )

This clause specifies optional parameters for the view; the following parameters are supported:
- check_option (string): This parameter can be local or cascaded, equivalent to specifying WITH [CASCADED | LOCAL ] CHECK OPTION (see below). You can change this option on an existing view using ALTER VIEW.
- security_barrier (boolean): This method should be used if the view is intended to provide row-level security.
- CONTINUOUS (boolean): If this parameter is specified, the view is a continuous aggregated materialized view.
- POPULATE (boolean): Whether the continuous clustering view synchronizes the source performance data. Default is true, if false is specified, it will only be synchronized if there is new insert data afterwards.

query

SELECT or VALUES commands that provide columns and rows of the view.

Note

The views in the YMatrix database are read-only. The system does not allow insertion, update or delete on views. You can get the effect of updating the view by creating the rewrite rules on the view as appropriate operations on other tables. For more information, see CREATE RULE.

Note that the name and data type of the view column will be assigned the way you want. For example:

CREATE VIEW vista AS SELECT 'Hello World';

There are two forms of error: the column name defaults to ?column?, and the column data type defaults to unknown. If you want to use string literals in the view results, use a method similar to the following:

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

Access to tables referenced in a view is determined by the permissions of the view owner rather than the current user (even if the current user is a superuser). For superusers, this can be confusing, as superusers can usually access all objects. In the case of a view, if the superuser is not the owner of the view, even the superuser must be explicitly granted access to the table referenced in the view.

However, the functions called in the view are handled in the same way as using the view to call them directly from the query. Therefore, the user of the view must have permission to call any function used by the view.

If you create a view using the ORDER BY clause, the ORDER BY clause is ignored when executing SELECT from the view.

When using CREATE OR REPLACE VIEW on an existing view, only the SELECT rules defined by the view are changed. Other view properties, including ownership, permissions, and non-SELECT rules, remain unchanged. You must have a view to replace it (including as a member who owns a role).

Example

Create a view of all comedy movies:

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

This creates a view that contains columns in the film table when the view is created. Although * is used to create a view, columns added to the table later will not belong to the view.

Create a view that gets the names of the top ten babies:

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

Create a recursive view of 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 that although the recursive view name in this CREATE VIEW command is pattern-qualified, its internal self-reference is not pattern-qualified. This is because the implicitly created CTE(Common Table Expressions) names cannot be schema-qualified.

If the CONTINUOUS option is specified, the Continuous Cluster View is:

CREATE VIEW cv1 WITH (CONTINUOUS, POPULATE=false) AS
    SELECT tag_id, COUNT(*), SUM(sensor)
    FROM metrics GROUP BY tag_id;

compatibility

The SQL standard specifies some additional features that are not available in the YMatrix database for the CREATE VIEW statement. The optional clauses for complete SQL commands in the standard are:

CHECK OPTION — This option is related to updating views. All INSERT and UPDATE commands on the view are checked to ensure that the data meets the view definition conditions (i.e., the new data will be visible through the view). If you do not, the update will be rejected.
LOCAL — Check integrity on this view.
CASCADED — Check integrity on this view and any dependent views. If CASCADED or LOCAL is not specified, it is assumed to be CASCADED.

CREATE OR REPLACE VIEW is an extension of the YMatrix database language. The same is true for the concept of temporary views.