MogDB
Ecological Tools
Doc Menu

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

None

Syntax

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

img NOTE: You can use WITH (security_barriers) to create a relatively secure view. This prevents attackers from printing hidden base table data by using the RAISE statement of low-cost functions.

Parameter Description

  • OR REPLACE

    Redefines the view if it already exists.

  • 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 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 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 the view.
mogdb=# SELECT * FROM myView ;

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