視圖管理
更新時間 2025-02-14 10:22:17
最近更新時間: 2025-02-14 10:22:17
分享文章
本文為您介紹如何管理視圖，包括創建視圖、刪除視圖和物化視圖使用。
創建視圖
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)
