视图管理
更新时间 2025-02-14 10:22:01
最近更新时间: 2025-02-14 10:22:01
本文为您介绍如何管理视图，包括创建视图、删除视图和物化视图使用。
创建视图
teledb=# create view t_range_view as select * from t_range;
CREATE VIEW
teledb=# select * from t_range_view;
f1 | f2 | f3
----+--------------------------+-----
1 | 2023-08-22 11:06:16.9783 | 1
2 | 2023-08-22 11:06:16.9783 | 50
2 | 2023-08-22 11:06:16.9783 | 110
3 | 2023-08-22 11:06:16.9783 | 100
(4 rows)数据类型重定义。
teledb=# drop view t_range_view;
DROP VIEW
teledb=# create view t_range_view as select f1,f2::date from t_range;
CREATE VIEW
teledb=# select * from t_range_view;
f1 | f2
----+------------
1 | 2023-08-22
3 | 2023-08-22
2 | 2023-08-22
2 | 2023-08-22
(4 rows)数据类型重定义，以及取别名。
teledb=# drop view t_range_view;
DROP VIEW
teledb=# create view t_range_view as select f1,f2::date as mydate from t_range;
CREATE VIEW
teledb=# select * from t_range_view;
f1 | mydate
----+------------
1 | 2023-08-22
2 | 2023-08-22
2 | 2023-08-22
3 | 2023-08-22
(4 rows)TeleDB支持视图引用表或字段改名联动，不受影响。
teledb=# \d+ t_range_view
View "teledb.t_range_view"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+--------+-----------+----------+---------+---------+-------------
f1 | bigint | | | | plain |
mydate | date | | | | plain |
View definition:
SELECT t_range.f1,
t_range.f2::date AS mydate
FROM t_range;
teledb=# alter table t_range rename to t_new;
ALTER TABLE
teledb=# alter table t_new rename f2 to f2_new;
ALTER TABLE
teledb=# \d+ t_range_view
View "teledb.t_range_view"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+--------+-----------+----------+---------+---------+-------------
f1 | bigint | | | | plain |
mydate | date | | | | plain |
View definition:
SELECT t_new.f1,
t_new.f2_new::date AS mydate
FROM t_new;删除视图
teledb=# create view t_range_view_new as select f1,f2_new::date as mydate from t_new;
CREATE VIEW
teledb=# drop view t_range_view_new;
DROP VIEW使用cascade 强制删除依赖对象。
teledb=# create view t_view as select * from t_range_view;
CREATE VIEW
teledb=# drop view t_range_view;
ERROR: cannot drop view t_range_view because other objects depend on it
DETAIL: view t_view depends on view t_range_view
HINT: Use DROP ... CASCADE to drop the dependent objects too.
teledb=# drop view t_range_view cascade;
NOTICE: drop cascades to view t_view
DROP VIEW物化视图使用
创建物化视图
teledb=# CREATE MATERIALIZED VIEW t_range_mv AS select f1,f2_new::date from t_new; SELECT 4访问物化视图
teledb=# select * from t_range_mv; f1 | f2_new ----+------------ 1 | 2023-08-22 3 | 2023-08-22 2 | 2023-08-22 2 | 2023-08-22 (4 rows)增量数据刷新
teledb=# insert into t_new(f1,f3) values(5,10); INSERT 0 1 teledb=# select * from t_new; f1 | f2_new | f3 ----+----------------------------+----- 1 | 2023-08-22 11:06:16.9783 | 1 3 | 2023-08-22 11:06:16.9783 | 100 2 | 2023-08-22 11:06:16.9783 | 50 2 | 2023-08-22 11:06:16.9783 | 110 5 | 2023-08-22 11:16:06.712109 | 10 (5 rows) teledb=# select * from t_range_mv; f1 | f2_new ----+------------ 1 | 2023-08-22 3 | 2023-08-22 2 | 2023-08-22 2 | 2023-08-22 (4 rows) teledb=# REFRESH MATERIALIZED VIEW t_range_mv; REFRESH MATERIALIZED VIEW teledb=# select * from t_range_mv; f1 | f2_new ----+------------ 1 | 2023-08-22 2 | 2023-08-22 2 | 2023-08-22 5 | 2023-08-22 3 | 2023-08-22 (5 rows)注意
物化视图数据存储在CN节点上面，每个CN节点各有一份相同的数据。
teledb=# explain select * from t_range_mv; QUERY PLAN --------------------------------------------------------------- Seq Scan on t_range_mv (cost=0.00..22.70 rows=1270 width=12) (1 row)
