ALTER VIEW

Function

ALTER VIEW changes various auxiliary attributes of the view. (If the user is changing the query definition of a view, use CREATE OR REPLACE VIEW.)

Precautions

The ALTER VIEW command can be executed only by the owner of the view or by a user who has been granted the view ALTER privilege, which the system administrator has by default. There are also the following privilege constraints on the attributes to be modified:

• To modify the schema of a view, the current user must be the owner of the view or the system administrator, and must have the CREATE privilege for the new schema, and must not create a naming conflict with an existing synonym in the new schema.
• To modify the owner of a view, the current user must be the owner or system administrator of the view, and the user must be a member of the new owner role, and this role must have the CREATE permission for the schema in which the view resides.
• Modify the naming of the view so that there is no naming conflict with a synonym that already exists in the current schema.

Add can specify the ALGORITHM option syntax.

Syntax

• Sets the default values for the view columns.

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [ IF EXISTS ] view_name
      ALTER [ COLUMN ] column_name SET DEFAULT expression;

• Cancels the default values for column view columns.

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [ IF EXISTS ] view_name
      ALTER [ COLUMN ] column_name DROP DEFAULT;

• Modify the owner of the view.

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [ IF EXISTS ] view_name 
      OWNER TO new_owner;

• Rename the view.

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [ IF EXISTS ] view_name 
      RENAME TO new_name;

• Sets the mode to which the view belongs.

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [ IF EXISTS ] view_name 
     SET SCHEMA new_schema;

• Sets the options for the view.

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [ IF EXISTS ] view_name
      SET ( { view_option_name [ = view_option_value ] } [, ... ] );

• Resets the view's options.

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [ IF EXISTS ] view_name
      RESET ( view_option_name [, ... ] );

• Set the definition of the view (this syntax is only supported in B-compatible mode)

  ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] VIEW view_name [ ( column_name [, ...] ) ]
    AS query [WITH [CASCADE | LOCAL] CHECK OPTION];

  Note:

  The new query in ALTER VIEW AS cannot change the column definitions of the original query, including order, column name, data type, type precision, etc. You can only add other columns at the end of the list.

Parameter Description

• IF EXISTS

  With this option, no error will be generated if the view does not exist, only a message will be displayed.

• ALGORITHM

  Specify the algorithm, options: UNDEFINED, MERGE, TEMPTABLE, currently only for syntax compatibility, no actual function.

• view_name

  The name of the view, can be modified by a pattern.

  Range of values: string, conforms to identifier naming convention.

• column_name

  Optional list of names, field names for the view. If not given, the field name is taken from the field name in the query.

  Range of values: string, conforming to the identifier naming convention.

• SET/DROP DEFAULT

  Sets or deletes the default value of a column; this parameter has no practical significance at this time.

• new_owner

  The user name of the new owner of the view.

• new_name

  The new name of the view.

• new_schema

  The new schema for the view.

• view_option_name [ = view_option_value ]

  This clause specifies an optional parameter for the view.

  • security_barrier

    This parameter should be used when the VIEW attempts to provide row-level security.

    Range of values: Boolean type, TRUE, FALSE.

  • check_option

    Specifies the checking options for this view.

    Range of values: LOCAL, CASCADED.

Examples

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

-- Modify the view name.
MogDB=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

-- Modify the schema to which the view belongs.
MogDB=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

-- Delete the view
MogDB=# DROP VIEW public.customer_details_view_v2;

Helpful Links

CREATE VIEW, DROP VIEW