ALTER VIEW

Description

ALTER VIEW alters 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 with the ALTER permission can run the ALTER VIEW command. System administrators have this permission by default. The following are permission restrictions depending on attributes to be modified:

To modify the schema of a view, you must be the owner of the view or a 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 a 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 that complies with the Identifier Naming Conventions.
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 that complies with the Identifier Naming Conventions.
SET/DROP DEFAULT
Sets or drops 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. Use this parameter when the view provides row-level security.

Value range: Boolean type, TRUE or FALSE.

Examples

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

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

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

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

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

-- Drop a view.
openGauss=# DROP VIEW public.customer_details_view_v2;

-- Drop the tpcds.customer table.
openGauss=# DROP TABLE tpcds.customer;

-- Drop the schema.
openGauss=# DROP SCHEMA tpcds CASCADE;