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

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

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.

After a view is created, you are not allowed to use REPLACE to modify column names in the view or delete the columns.

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

  

  If query contains a clause specifying the partition of a partitioned table, the OID of the specified partition is hardcoded to the system catalog when the view is created. If a DDL operation that will change the OID of the specified partition is used, for example, DROP, SPLIT, or MERGE, the view is unavailable. In this case, you need to create a view.

Examples

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

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

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

Helpful Links

ALTER VIEW and DROP VIEW