Help Center> Data Warehouse Service> SQL Syntax Reference> DDL Syntax> CREATE VIEW
View PDF

CREATE VIEW

Function

CREATE VIEW creates a view. A view is a virtual table, not a base table. A database only stores the definition of a view and does not store its data. The data is still stored in the original 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

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

When creating a view, 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 costs functions.

Parameter Description

  • OR REPLACE

    Redefines a view if there is already a view.

  • TEMP | TEMPORARY

    Creates a temporary view.

  • view_name

    Specifies the name of a view to be created. It is optionally schema-qualified.

    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]

    This clause specifies optional parameters for a view.

    Currently, the only parameter supported by view_option_name is security_barrier, which should be enabled when a view is intended to provide row-level security.

    Value range: Boolean type. It can be TRUE or FALSE.

  • query

    A SELECT or VALUES statement which will provide the columns and rows of the view.

Examples

1
2
3
4
5
6
7
8
9
 
-- Create a view consisting of columns whose spcname is pg_default:
CREATE VIEW myView AS
    SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

-- Query a view:
SELECT * FROM myView ;

-- Delete the myView view:
DROP VIEW myView;

Helpful Links

ALTER VIEW, DROP VIEW

Parent topic: DDL Syntax