CREATE VIEW

Description

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.

• You can define frequently used data as views to encapsulate complex query SQL statements, which simplifies operations.
• You can query only data defined in a view, which is secure. Columns in a base table are hidden to protect the data structure of the database.
• User permission management is simplified. Only the permission to use views is granted to users.

Precautions

A user granted with the CREATE ANY TABLE permission can create views in the public and user schemas.

Syntax

1
2
3
4

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

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.

Parameters

• OR REPLACE

  Optional. Redefines the view if it already exists.

• TEMP | TEMPORARY

  (Optional) Creates a temporary view. The view is automatically deleted when the current session ends. If any table referenced by a view is a temporary table, the view is created as a temporary view (regardless of whether TEMP|TEMPORARY is specified in the SQL statement).

• 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 security_barrier and check_option.

  security_barrier: This parameter is used when the view attempts to provide row-level security. Value range: Boolean (true or false).

  check_option: Controls the behavior of updating a view. Value range: CASCADED and LOCAL.

• 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 fixed 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

• Common view

  -- Create the test_tb1 table and insert 100 data records into the table.
  gaussdb=# CREATE TABLE test_tb1(col1 int, col2 int);
  gaussdb=# INSERT INTO test_tb1 VALUES (generate_series(1,100),generate_series(1,100));
  -- Create a view whose value of col1 is less than 3.
  gaussdb=# CREATE VIEW test_v1 AS SELECT * FROM test_tb1 WHERE col1 < 3;
  -- Query a view.
  gaussdb=# SELECT * FROM test_v1;
   col1 | col2 
  ------+------
      1 |    1
      2 |    2
  (2 rows)
  
  -- Delete the table and the view.
  gaussdb=# DROP VIEW test_v1;
  gaussdb=# DROP TABLE test_tb1;

• Temporary view

  -- Create a table and a temporary view.
  gaussdb=# CREATE TABLE test_tb2(c1 int, c2 int);
  gaussdb=# CREATE TEMP VIEW test_v2 AS SELECT * FROM test_tb2;
  -- Query the table and view information. (The temporary table belongs to a schema starting with pg_temp instead of public.)
  gaussdb=# \d
                                      List of relations
             Schema           |   Name   | Type  | Owner |             Storage              
  ----------------------------+----------+-------+-------+----------------------------------
   pg_temp_cn_5001_6_2_187504 | test_v2  | view  | omm   | 
   public                     | test_tb2 | table | omm   | {orientation=row,compression=no}
  (2 rows)
  
  -- Exit the current session and log in again. Check whether the temporary view is deleted.
  gaussdb=# \d
                                      List of relations
   Schema |   Name   | Type  | Owner |                       Storage                        
  --------+----------+-------+-------+------------------------------------------------------
   public | test_tb2 | table | omm   | {orientation=row,compression=no,storage_type=USTORE}
  (1 row)
  
  -- Drop the table.
  gaussdb=# DROP TABLE test_tb2;

Helpful Links

ALTER VIEW and DROP VIEW