ALTER VIEW

Description

ALTER VIEW modifies all auxiliary attributes of a view. (To modify the query definition of a view, use CREATE OR REPLACE VIEW.)

Precautions

Only the view owner or a user granted with the ALTER permission can run the ALTER VIEW command. The 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.
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.
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 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.
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.

Currently, view_option_name supports only the security_barrier parameter. This parameter is used when the view attempts to provide row-level security.

Value range: Boolean type, TRUE, and FALSE.

Examples

-- Create a schema.
gaussdb=# CREATE SCHEMA tpcds;

-- Create the tpcds.customer table.
gaussdb=# CREATE TABLE tpcds.customer
(
c_customer_sk         INTEGER           NOT NULL,
c_customer_id         CHARACTER(16)     NOT NULL
);
 
-- Insert multiple records into the table.
gaussdb=# INSERT INTO tpcds.customer VALUES (1, 'AAAAAAAABAAAAAAA'),(100, 'AAAAAAAACAAAAAAA'),(150, 'AAAAAAAADAAAAAAA');

-- Create a view consisting of rows with c_customer_sk less than 150.
gaussdb=# CREATE VIEW tpcds.customer_details_view_v1 AS
    SELECT * FROM tpcds.customer
    WHERE c_customer_sk < 150;

-- Rename a view.
gaussdb=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

-- Change the schema of a view.
gaussdb=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

-- Delete a view.
gaussdb=# DROP VIEW public.customer_details_view_v2;

-- Delete the pcds.customer table.
gaussdb=# DROP TABLE tpcds.customer;

-- Delete a schema.
gaussdb=# DROP SCHEMA tpcds CASCADE;