CREATE VIEW

Description

Creates a view. A view is a virtual 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

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

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

• 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_dn_6001_1_1_9473 | test_v2  | view  | omm   | 
   public                   | test_tb2 | table | omm   | {orientation=row,compression=no,storage_type=USTORE}
  (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)
  
  -- Delete the table.
  gaussdb=# DROP TABLE test_tb2;

• Insert, update, and delete views.

  -- Create a single table view.
  gaussdb=# CREATE TABLE t_view_iud1 (x int, y int);
  CREATE TABLE
  gaussdb=# INSERT INTO t_view_iud1 VALUES (11, 11);
  INSERT 0 1
  gaussdb=# CREATE VIEW vt AS SELECT * FROM t_view_iud1;
  CREATE VIEW
  gaussdb=# CREATE VIEW vt_wco AS SELECT * FROM t_view_iud1 WHERE x > 5 WITH CHECK OPTION;
  CREATE VIEW
  
  -- View the information_schema.columns view and check whether columns in the view can be updated.
  gaussdb=# SELECT table_schema, table_name, column_name, is_updatable FROM information_schema.columns WHERE table_schema = current_schema AND table_name = 'vt';
   table_schema | table_name | column_name | is_updatable
  --------------+------------+-------------+--------------
   public       | vt         | y           | YES
   public       | vt         | x           | YES
  (2 rows)
  
  -- Insert and update data in a view. If WITH CHECK OPTION is specified when a view is created, data is checked before the view is updated.
  gaussdb=# INSERT INTO vt VALUES (1, 1);
  INSERT 0 1
  gaussdb=# INSERT INTO vt_wco VALUES (1, 1);
  ERROR:  new row violates WITH CHECK OPTION for view "vt_wco"
  DETAIL:  Failing row contains (1, 1).
  gaussdb=# UPDATE vt SET y = 121 WHERE y = 1;
  UPDATE 1
  gaussdb=# UPDATE vt_wco SET y = 6 WHERE y = 11;
  UPDATE 1
  gaussdb=# DELETE FROM vt WHERE y =11;
  DELETE 0
  
  -- Create a multi-layer nested view and specify WITH CHECK OPTION in the bottom-layer relationship. Check the corresponding conditions when DML operations are performed on the top-layer view.
  gaussdb=# CREATE VIEW vvtt AS SELECT * FROM vt_wco;
  CREATE VIEW
  gaussdb=# INSERT INTO vvtt VALUES (1, 1), (2, 2);
  ERROR:  new row violates WITH CHECK OPTION for view "vt_wco"
  DETAIL:  Failing row contains (1, 1).
  
  -- View information_schema.tables and information_schema.views and check whether they can be inserted and updated.
  gaussdb=# SELECT table_schema, table_name, is_insertable_into FROM information_schema.tables
  gaussdb-# WHERE table_schema = current_schema AND table_name = 'vvtt';
   table_schema | table_name | is_insertable_into
  --------------+------------+--------------------
   public       | vvtt       | YES
  (1 row)
  
  gaussdb=# SELECT table_name, is_updatable, check_option FROM information_schema.views
  gaussdb-# WHERE table_schema = current_schema AND table_name = 'vvtt';
   table_name | is_updatable | check_option
  ------------+--------------+--------------
   vvtt       | YES          | NONE
  (1 row)
  
  gaussdb=# SELECT table_name, is_updatable, check_option FROM information_schema.views WHERE table_schema = current_schema AND table_name = 'vt_wco';
   table_name | is_updatable | check_option
  ------------+--------------+--------------
   vt_wco     | YES          | CASCADED
  (1 row)
  
  -- Delete the view and table.
  gaussdb=# DROP VIEW vvtt, vt, vt_wco CASCADE;
  DROP VIEW
  gaussdb=# DROP TABLE t_view_iud1;
  DROP TABLE

Helpful Links

ALTER VIEW and DROP VIEW