CREATE VIEW

Function

CREATE VIEW creates a view. A view is a virtual table, not a base table. Only view definition is stored in the database and view data is not. The data is stored in a base table. If data in the base table changes, the data in the view changes accordingly. In this sense, a view is like a window through which users can know their interested data and data changes in the database.

Precautions

A user granted with the CREATE ANY TABLE permission can create views in the public and user schemas.

Syntax

CreateView ::= CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
    [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
    AS query;

NOTE:

• You can use WITH(security_barrier) to create a relatively secure view. This prevents attackers from printing hidden base table data by using the RAISE statement of low-cost functions.
• In MogDB old version, when a view is created by executing create or replace statement, if there are views with the same name, you cannot modify or delete column information when replacing it. You can only add columns. Since version 2.1, MogDB supports REPLACE VIEW syntax to update or delete column information.

Parameter Description

• OR REPLACE

  Redefines the view if it already exists. Supports operations to delete columns and change column names, only valid for non-materialized views.

• TEMP | TEMPORARY

  Creates a temporary view.

• view_name

  Specifies the name (optionally schema-qualified) of the view to be created.

  Value range: a string. It must comply with the identifier naming convention.

• column_name

  Specifies an optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.

  Value range: a string. It must comply with the identifier naming convention.

• view_option_name [= view_option_value]

  Specifies an optional parameter for a view.

  Currently, view_option_name supports only the security_barrier parameter. This parameter is used when the view attempts to provide row-level security.

  Value range: Boolean type, TRUE, and FALSE.

• query

  Specifies a SELECT or VALUES statement that will provide the columns and rows of the view.

Examples

-- Create a view consisting of columns whose spcname is pg_default.
MogDB=# CREATE VIEW myView AS
    SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

-- Query a view.
MogDB=# SELECT * FROM myView ;

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

Helpful Links

ALTER VIEW and DROP VIEW