• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. ALTER VIEW

ALTER VIEW

Function

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 can modify a view by running ALTER VIEW.
  • To change a view's schema, you must also have the CREATE permission on the new schema.
  • To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the view's schema.
  • An administrator can change the owner relationship of any view.

Syntax

  • Set the default value of the view column.
    ALTER VIEW [ IF EXISTS ] view_name
        ALTER [ COLUMN ] column_name SET DEFAULT expression;
  • Remove the default value of the 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 the view.
    ALTER VIEW [ IF EXISTS ] view_name 
        SET SCHEMA new_schema;
  • Set the options of the view.
    ALTER VIEW [ IF EXISTS ] view_name
        SET ( { view_option_name [ = view_option_value ] } [, ... ] );
  • Reset the options of the view.
    ALTER VIEW [ IF EXISTS ] view_name
        RESET ( view_option_name [, ... ] );

Parameter Description

  • IF EXISTS

    If this option is specified, no error is reported if the view does not exist. 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 rule.

  • column_name

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

  • SET/DROP DEFAULT

    Sets or deletes the default value of a column. Currently, 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 ]

    This clause specifies optional parameters for a view.

    Currently, the only parameter supported by view_option_name is security_barrier, which should be enabled when a view is intended to provide row-level security.

    Value range: Boolean type. It can be TRUE or FALSE.

Examples

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

-- Rename the view:
ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

-- Change the schema of the view:
ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

-- Delete the view:
DROP VIEW public.customer_details_view_v2;

Helpful Links

CREATE VIEW, DROP VIEW