CREATE VIEW

Function

Create a view. A view, unlike a basic table, is a virtual table. Only the definition of the view is stored in the database, but not the data corresponding to the view, which is still stored in the original basic table. If the data in the basic table changes, the data queried from the view also changes. In this sense, the view is like a window through which you can see the data and changes in the database that are of interest to the user.

Precautions

Users granted the CREATE ANY TABLE privilege can create views in both public and user mode. No naming conflicts can be created with a synonym that already exists in the same schema.

Added the ability to specify the ALGORITHM option syntax.

Syntax

CREATE [ OR REPLACE ] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
    [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

Note:

• Use WITH(security_barrier) when creating a view to create a relatively secure view that prevents attackers from using the RAISE statement of the low-cost function to print out hidden base table data.
• When the view is created, you are not allowed to use REPLACE to modify the column names in the view, nor are you allowed to delete columns.

Parameter Description

• OR REPLACE

  When OR REPLACE exists in CREATE VIEW, it means that the view is replaced if it previously existed, but the new query cannot change the column definitions of the original query, including the order, column name, data type, type precision, etc., and only other columns can be added at the end of the list.

• ALGORITHM

  Specify the algorithm, options: UNDEFINED, MERGE, TEMPTABLE. currently only do syntax compatibility, no actual function for the time bein

• DEFINER = user

  Specifies user as the owner of the view. This option is only available in B-compatible mode.

• TEMP | TEMPORARY

  Create a temporary view.

• view_name

  The name of the view to be created. Can be modified with a pattern.

  Range of values: string, conforming to the identifier naming convention.

• column_name

  Optional list of names to use as field names for the view. If not given, the field names are taken from the field names in the query.

  Range of values: string, conforming to the identifier naming convention.

• view_option_name [= view_option_value]

  This clause specifies an optional parameter for the view.

  • security_barrier

    This parameter should be used when the VIEW attempts to provide row-level security.

    Value range: Boolean type, TRUE, FALSE.

  • check_option

    Specifies the checking options for this view.

    Range of values: LOCAL, CASCADED.

• query

  SELECT or VALUES statements that provide rows and columns for the view.

• WITH [ CASCADED | LOCAL ] CHECK OPTION

  This option controls the behavior of automatic view updates. INSERT and UPDATE of a view are checked to ensure that the new row satisfies the conditions defined by the view, i.e., that the new row is visible through the view. If the check is not passed, the modification is rejected. If this option is not added, then inserts and updates to a view are allowed to create rows that are not visible through the view. The following checking options are supported:

  • LOCAL

    Only conditions directly defined by the view itself are checked, unless the underlying view also defines CHECK OPTION, none of the conditions defined by them are checked.

  • CASCADED

    Checks the conditions defined for this view and all underlying views. If only CHECK OPTION is declared and not LOCAL and CASCADED, the default is CASCADED.

    Notes:

    1. CHECK OPTION is supported only on views that can be auto-updated without INSTEAD OF triggers or INSTEAD rules. if an auto-updating view is defined on a view with INSTEAD OF triggers, CHECK OPTION can be used to check the conditions on the auto-updating view but the conditions on views with INSTEAD OF trigger will not be checked. If the view or any underlying relationship has an INSTEAD rule that causes the INSERT or UPDATE command to be rewritten, then all checking options will be ignored in the rewritten query, including any checking from the auto-updatable view defined on the relationship with the STEAD rule.
    2. The CHECK OPTION option is not supported for views based on MySQL foreign tables.

Automatically Updatable Views

Simple views are auto-updatable, and the system allows INSERT, UPDATE, and DELETE statements to be executed on such views. A view is auto-updatable if it meets the following conditions.

• The view has only one item in the FROM list and must either be a table or another auto-updatable view.
• The top level of the view definition cannot contain a view with a WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET clause
• The top level of a view definition cannot contain views with aggregate operations (UNION, INTERSET, EXCEPT).
• The target list of a view cannot contain aggregate functions, window functions, or functions that return a collection.

An auto-updatable view can have a mix of updatable columns as well as non-updatable columns. A column is updatable if it is a simple reference to an updatable column in the underlying relationship. Otherwise, the column is read-only and will report an error if an INSERT or UPDATE statement attempts to assign a value to it.

If the view is automatically updatable, the system converts any INSERT, UPDATE, or DELETE statement on the view into a corresponding statement on the underlying relationship.

If an auto-updatable view contains a WHERE condition, the condition restricts which rows of the underlying relationship can be modified by UPDATE and DELETE statements on the view. However, a row that is allowed to be modified by an UPDATE may make that row no longer satisfy the WHERE condition and therefore no longer be visible from the view. Similarly, an INSERT command may insert rows that do not satisfy the WHERE condition and therefore are not visible from the view. CHECK OPTION can be used to prevent INSERT and UPDATE commands from creating such rows that are not visible from the view.

A more complex view that does not fulfill the above conditions is read-only by default, and the system does not allow INSERT, UPDATE, and DELETE statements to be executed on that view. The effect of an updatable view can be obtained by creating an INSTEAD OF trigger on that view, which must convert an attempted insert on that view into a legal action on another table; see CREATE TRIGGER. Another way is to create rules (see CREATE RULE).

Note that the user performing an insert, update, or delete on a view must have the corresponding insert, update, or delete privilege on that view. In addition, the owner of the view must have the corresponding privileges on the underlying relationship, but the user performing the execution does not need any privileges on the underlying relationship.

Examples

-- Create a view consisting of the field spcname as pg_default.
MogDB=# CREATE VIEW myView AS
    SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

-- Check out the view.
MogDB=# SELECT * FROM myView ;

-- Delete myView.
MogDB=# DROP VIEW myView;

Helpful Links

ALTER VIEW, DROP VIEW