ALTER VIEW

Description

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

Click to enlarge

Remove the default value of a view column.

ALTER VIEW [ IF EXISTS ] view_name
    ALTER [ COLUMN ] column_name DROP DEFAULT;

Click to enlarge

Change the owner of a view.

ALTER VIEW [ IF EXISTS ] view_name 
    OWNER TO new_owner;

Click to enlarge

Rename a view.

ALTER VIEW [ IF EXISTS ] view_name 
    RENAME TO new_name;

Click to enlarge

Set the schema of a view.

ALTER VIEW [ IF EXISTS ] view_name 
    SET SCHEMA new_schema;

Click to enlarge

Set the options of a view.

ALTER VIEW [ IF EXISTS ] view_name
    SET ( { view_option_name [ = view_option_value ] } [, ... ] );

Click to enlarge

Reset the options of a view.

ALTER VIEW [ IF EXISTS ] view_name
    RESET ( view_option_name [, ... ] );

Click to enlarge

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;

-- Set the default value of the view column to 100.
gaussdb=# ALTER VIEW IF EXISTS  abc ALTER  COLUMN  col1 SET DEFAULT 100;

-- Cancel the default value of the view column.
gaussdb=# ALTER VIEW IF EXISTS  abc ALTER  COLUMN  col1 DROP DEFAULT;

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

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

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