Updated on 2024-06-03 GMT+08:00

ALTER VIEW

Description

ALTER VIEW changes the auxiliary attributes of a view. If you want to change the query definition of a view, use CREATE OR REPLACE VIEW.

Precautions

Only the view owner or a user granted the ALTER permission on a view can run the ALTER VIEW command. When separation of duties is disabled, a system administrator has this permission by default. The following are permission constraints depending on the attributes to be modified:
  • To modify the schema of a view, you must be the owner of the view or system administrator and have the CREATE permission on the new schema. When separation of duties is enabled, a system administrator cannot change the view mode.
  • To modify the owner of a view, you must be the owner of the view or system administrator and a member of the new owner role, with the CREATE permission on the schema of the view. When separation of duties is enabled, a system administrator cannot change the owner of a view.
  • Do not change the type of a column in a view.

Syntax

  • Set the default value of a view column.
    ALTER VIEW [ IF EXISTS ] view_name
        ALTER [ COLUMN ] column_name SET DEFAULT expression;

  • Remove the default value of a view column.
    ALTER VIEW [ IF EXISTS ] view_name
        ALTER [ COLUMN ] column_name DROP DEFAULT;

  • Change the owner of a view.
    ALTER VIEW [ IF EXISTS ] view_name 
        OWNER TO new_owner;

  • Rename a view.
    ALTER VIEW [ IF EXISTS ] view_name 
        RENAME TO new_name;

  • Set the schema of a view.
    ALTER VIEW [ IF EXISTS ] view_name 
        SET SCHEMA new_schema;

  • Set the options of a view.
    ALTER VIEW [ IF EXISTS ] view_name
        SET ( { view_option_name [ = view_option_value ] } [, ... ] );

  • Reset the options of a view.
    ALTER VIEW [ IF EXISTS ] view_name
        RESET ( view_option_name [, ... ] );

Parameters

  • IF EXISTS

    If this option is used, no error is generated when the view does not exist, and only a message is displayed.

  • view_name

    Specifies the view name, which can be schema-qualified.

    Value range: a string. It must be an existing view name.

  • column_name

    Specifies the column name.

    Value range: a string. It must be an existing view column name.

  • SET/DROP DEFAULT

    Sets or deletes the default value of a column. This parameter does not take effect.

  • new_owner

    Specifies the new owner of a view.

  • new_name

    Specifies the new view name.

  • new_schema

    Specifies the new schema of the view.

  • view_option_name [ = view_option_value ]

    Specifies an optional parameter for a view.

    • security_barrier: specifies whether the view provides row-level security. The value is of the Boolean type. The default value is true.
    • check_option: controls the behavior of updating a view. This parameter can be set to CASCADED or LOCAL. This parameter cannot be left blank.
  • expression

    Specifies constants, functions, or SQL expressions.

Examples

  • Rename a view.
    -- Create the test_tbl table.
    gaussdb=# CREATE TABLE test_tb1(col1 INT,col2 INT);
    
    -- Create a view.
    gaussdb=# CREATE VIEW abc AS SELECT * FROM test_tb1;
    
    -- Rename the view.
    gaussdb=# ALTER VIEW IF EXISTS abc RENAME TO test_v1;
    
    -- Query the view.
    gaussdb=# \dv
                 List of relations
     Schema |  Name   | Type | Owner | Storage 
    --------+---------+------+-------+---------
     public | test_v1 | view | omm   | 
    (1 row)
  • Change the owner of the view.
    -- Create a user.
    gaussdb=# CREATE ROLE role_test PASSWORD '********';
    
    -- Change the owner of the view.
    gaussdb=# ALTER VIEW IF EXISTS test_v1 OWNER TO role_test;
    
    -- Query the view information.
    gaussdb=# \dv
                   List of relations
     Schema |  Name   | Type |   Owner   | Storage 
    --------+---------+------+-----------+---------
     public | test_v1 | view | role_test | 
    (1 row)
  • Set the schema of the view.
    -- Create a schema.
    gaussdb=# CREATE SCHEMA tcpds;
    
    -- Change the schema of the view.
    gaussdb=# ALTER VIEW test_v1 SET SCHEMA tcpds;
    
    -- Query the view information.
    gaussdb=# \dv tcpds.test_v1;
                   List of relations
     Schema |  Name   | Type |   Owner   | Storage 
    --------+---------+------+-----------+---------
     tcpds  | test_v1 | view | role_test | 
    (1 row)
  • Set and reset view options.
    -- Modify view options.
    gaussdb=# ALTER VIEW tcpds.test_v1 SET (security_barrier = TRUE);
    ALTER VIEW
    
    -- Query.
    gaussdb=# \dv tcpds.test_v1;
                         List of relations
     Schema |  Name   | Type | Owner  |         Storage
    --------+---------+------+--------+-------------------------
     tcpds  | test_v1 | view | chenxi | {security_barrier=true}
    (1 row)
    
    -- Modify the check_option option.
    gaussdb=# ALTER VIEW tcpds.test_v1 SET (check_option = 'LOCAL');
    ALTER VIEW
    
    -- Query.
    gaussdb=# \dv tcpds.test_v1;
                                   List of relations
     Schema |  Name   | Type | Owner  |                  Storage
    --------+---------+------+--------+--------------------------------------------
     tcpds  | test_v1 | view | chenxi | {security_barrier=true,check_option=LOCAL}
    (1 row)
    
    -- Reset view options.
    gaussdb=# ALTER VIEW tcpds.test_v1 RESET (security_barrier);
    ALTER VIEW
    gaussdb=# ALTER VIEW tcpds.test_v1 RESET (check_option);
    ALTER VIEW
    
    -- Query.
    gaussdb=# \dv tcpds.test_v1;
                 List of relations
     Schema |  Name   | Type | Owner  | Storage
    --------+---------+------+--------+---------
     tcpds  | test_v1 | view | chenxi |
    (1 row)
    
    -- Delete the test_v1 view.
    gaussdb=# DROP VIEW tcpds.test_v1;
    DROP VIEW
    
    -- Delete the test_tb1 table.
    gaussdb=# DROP TABLE test_tb1;
    DROP TABLE
    
    -- Delete the user.
    gaussdb=# DROP ROLE role_test;
    DROP ROLE
    
    -- Delete the schema.
    gaussdb=# DROP SCHEMA tcpds;
    DROP SCHEMA

Helpful Links

CREATE VIEW and DROP VIEW