ALTER MATERIALIZED VIEW

Description

Modifies multiple auxiliary attributes of an existing materialized view.

Statements and actions that can be used for ALTER MATERIALIZED VIEW are a subset of ALTER TABLE and have the same meaning when used for materialized views. For details, see ALTER TABLE.

Precautions

• Only the owner of a materialized view or a system administrator has the ALTER MATERIALIZED VIEW permission.
• The materialized view structure cannot be modified.

Syntax

• Change the owner of a materialized view.

  ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
      OWNER TO new_owner;

  

• Rename a column of a materialized view.

  ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
      RENAME [ COLUMN ] column_name TO new_column_name;

  

• Rename a materialized view.

  ALTER MATERIALIZED VIEW [ IF EXISTS ] mv_name
      RENAME TO new_name;

  

Parameters

• mv_name

  Specifies the name of an existing materialized view, which can be schema-qualified.

  Value range: a string. It must comply with the naming convention.

• column_name

  Specifies the name of a new or existing column.

  Value range: a string. It must comply with the naming convention.

• new_column_name

  Specifies the new name of an existing column.

• new_owner

  Specifies the username of the new owner of a materialized view.

• new_name

  Specifies the new name of a materialized view.

Examples

• Change the owner of a materialized view.

  -- Create a table.
  gaussdb=# CREATE TABLE my_table (c1 int, c2 int) WITH(STORAGE_TYPE=ASTORE);
  
  -- Create a complete-refresh materialized view.
  gaussdb=# CREATE MATERIALIZED VIEW foo AS SELECT * FROM my_table;
  
  -- Create a user.
  gaussdb=# CREATE USER test PASSWORD '********';
  
  -- Change the owner of the complete-refresh materialized view.
  gaussdb=# ALTER MATERIALIZED VIEW foo OWNER TO test;
  
  -- View the information about the materialized view.
  gaussdb=# \dm foo
                                List of relations
   Schema | Name |       Type        | Owner |             Storage              
  --------+------+-------------------+-------+----------------------------------
   public | foo  | materialized view | test  | {orientation=row,compression=no}
  (1 row)

• Rename a column of a materialized view.

  -- Query the columns of the materialized view.
  gaussdb=# \d foo;
  Materialized view "public.foo"
   Column |  Type   | Modifiers 
  --------+---------+-----------
   c1     | integer | 
   c2     | integer | 
  Rules:
      "_RETURN" AS
      ON SELECT TO foo DO INSTEAD  SELECT my_table.c1, my_table.c2
     FROM my_table
  Replica Identity: NOTHING
  
  -- Change column c1 of materialized view foo to col1 and column c2 to col2.
  gaussdb=# ALTER MATERIALIZED VIEW foo RENAME c1 to col1;
  gaussdb=# ALTER MATERIALIZED VIEW foo RENAME c2 to col2;
  
  -- Use the SELECT statement to view the columns of the MV.
  gaussdb=# SELECT * FROM foo WHERE 1=2;
   col1 | col2 
  ------+------
  (0 rows)

• Rename a materialized view.

  -- Rename the materialized view foo to my_mview.
  gaussdb=# ALTER MATERIALIZED VIEW foo RENAME TO my_mview;
  
  -- Query information.
  gaussdb=# \dm my_mview
                                  List of relations
   Schema |   Name   |       Type        | Owner |             Storage              
  --------+----------+-------------------+-------+----------------------------------
   public | my_mview | materialized view | test  | {orientation=row,compression=no}
  (1 row)
  
  -- Delete.
  gaussdb=# DROP MATERIALIZED VIEW my_mview;
  gaussdb=# DROP TABLE my_table ;
  gaussdb=# DROP USER test;

Helpful Links

CREATE INCREMENTAL MATERIALIZED VIEW, CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH INCREMENTAL MATERIALIZED VIEW, and REFRESH MATERIALIZED VIEW