云数据库 GaussDB-ALTER MATERIALIZED VIEW:示例

时间：2025-02-12 15:07:56

云数据库 GaussDB

示例

修改物化视图所有者。

--创建表。gaussdb=# CREATE TABLE my_table (c1 int, c2 int) WITH(STORAGE_TYPE=ASTORE);--创建全量物化视图。gaussdb=# CREATE MATERIALIZED VIEW foo AS SELECT * FROM my_table;--创建用户。gaussdb=# CREATE USER test PASSWORD '********';--修改全量物化视图的所有者。gaussdb=# ALTER MATERIALIZED VIEW foo OWNER TO test;--查看物化视图信息。gaussdb=# \dm foo                              List of relations Schema | Name |       Type        | Owner |             Storage              --------+------+-------------------+-------+---------------------------------- public | foo  | materialized view | test  | {orientation=row,compression=no}(1 row)

重命名物化视图的列。

--查询物化视图的列。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_tableReplica Identity: NOTHING--将物化视图foo的列c1修改为col1，c2修改为col2。gaussdb=# ALTER MATERIALIZED VIEW foo RENAME c1 to col1;gaussdb=# ALTER MATERIALIZED VIEW foo RENAME c2 to col2;--通过SELECT查看该物化视图的列。gaussdb=# SELECT * FROM foo WHERE 1=2; col1 | col2 ------+------(0 rows)

重命名物化视图。

--将物化视图foo重命名为my_mview。gaussdb=# ALTER MATERIALIZED VIEW foo RENAME TO my_mview;--查询信息。gaussdb=# \dm my_mview                                List of relations Schema |   Name   |       Type        | Owner |             Storage              --------+----------+-------------------+-------+---------------------------------- public | my_mview | materialized view | test  | {orientation=row,compression=no}(1 row)--删除。gaussdb=# DROP MATERIALIZED VIEW my_mview;gaussdb=# DROP TABLE my_table ;gaussdb=# DROP USER test;

上一篇：云数据库 GaussDB-ALTER COLUMN ENCRYPTION KEY:参数说明

下一篇：云数据库 GaussDB-SQL语法格式说明