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;

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.
postgres=# CREATE VIEW myView AS
    SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

-- Query the view.
postgres=# SELECT * FROM myView ;

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

Helpful Links

ALTER VIEW and DROP VIEW