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;

Click to enlarge

Rename a column of a materialized view.

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

Click to enlarge

Rename a materialized view.

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

Click to enlarge

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 materialized view.
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;