ALTER VIEW

Description

Modifies the auxiliary attributes of a view.

Precautions

Only the view owner or users with the ALTER permission on the current view can run the ALTER VIEW command. System administrators automatically have this permission when separation of duties is disabled. The following are permission constraints depending on the attributes to be modified:

To modify the schema of a view, you need to be either the view owner or a system administrator and have the CREATE permission on the new schema. However, when separation of duties is enabled, system administrators are restricted from making this modification.
To modify the owner of a view, you need to be either the view owner or a system administrator. Additionally, you must be a member of the new owner's role and have the CREATE permission on the schema of the view. However, when separation of duties is enabled, system administrators are restricted from making this modification.
Do not modify column names and column types or delete columns from a view that cannot be updated. You can modify column names and column types or delete columns from a view that can be updated.
You are advised not to modify column names and column types or delete columns from the bottom-layer view of a nested view. Otherwise, the upper-layer view will be unavailable.

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;

Modify 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 definition of a view.
```
ALTER VIEW view_name [ ( column_name [, ...] ) ]
    AS query [WITH [CASCADED | LOCAL] CHECK OPTION];
```
CREATE OR REPLACE VIEW is used to change the query definition of a view. Both have the same function.

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: existing view names (character string).
column_name
Specifies the column name.

Value range: existing column names (character string).
SET/DROP DEFAULT
Sets or deletes the default value of a column. This parameter does not take effect.
new_owner
Sets a username for the new owner of a view. The username must meet the requirements outlined in •user_name.
new_name
Specifies the new view name.
new_schema
Specifies the new schema of the view.
expression
Specifies constants, functions, or SQL expressions.
WITH [ CASCADED | LOCAL ] CHECK OPTION
Controls the behavior of updating a view. The options are as follows:

CASCADED: New rows are checked against the conditions of the view and all underlying views. If CHECK OPTION is specified, and neither LOCAL nor CASCADED is specified, then CASCADED is used by default.

LOCAL: New rows are only checked against the conditions defined directly on the view itself. Any conditions defined on underlying views are not checked (unless they also specify CHECK OPTION).

Example

Rename a view.

-- Create the test_tbl table.
m_db=# CREATE TABLE test_tb1(col1 INT,col2 INT);
-- Create a view.
m_db=# CREATE VIEW abc AS SELECT * FROM test_tb1;
-- Rename the view.
m_db=# ALTER VIEW IF EXISTS abc RENAME TO test_v1;
-- Query a view.
m_db=# \dv
             List of relations
 Schema |  Name   | Type | Owner | Storage 
--------+---------+------+-------+---------
 public | test_v1 | view | omm   | 
(1 row)

Modify the owner of a view.

-- Create a user.
m_db=# CREATE ROLE role_test PASSWORD '********';
-- Modify the owner of a view.
m_db=# ALTER VIEW IF EXISTS test_v1 OWNER TO role_test;
-- Query the view information.
m_db=# \dv
               List of relations
 Schema |  Name   | Type |   Owner   | Storage 
--------+---------+------+-----------+---------
 public | test_v1 | view | role_test | 
(1 row)

Set the schema of a view.

-- Create a schema.
m_db=# CREATE SCHEMA tcpds;
-- Modify the schema of a view.
m_db=# ALTER VIEW test_v1 SET SCHEMA tcpds;
-- Query the view information.
m_db=# \dv tcpds.test_v1;
               List of relations
 Schema |  Name   | Type |   Owner   | Storage 
--------+---------+------+-----------+---------
 tcpds  | test_v1 | view | role_test | 
(1 row)