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

1
2
3

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 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: TRUE or 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 the partition DDL syntax that causes the change in 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

1
2
3
4
5
6
7
8
9

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

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

Helpful Links

ALTER VIEW and DROP VIEW