创建视图

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)