一、创建视图
数据类型重定义。
postgres=# create view t_range_view as select f1,f2::date from t_range;CREATE VIEWpostgres=# select * from t_range_view; f1 | f2 —-+———— 1 | 2017-09-27 2 | 2017-09-27 2 | 2017-09-27 1 | 2017-09-27 3 | 2017-09-27(5 rows)
数据类型重定义，以及取别名。
postgres=# create view t_range_view as select f1,f2::date as mydate from t_range;CREATE VIEWpostgres=# select * from t_range_view; f1 | mydate —-+———— 1 | 2017-09-27 2 | 2017-09-27 2 | 2017-09-27 1 | 2017-09-27 3 | 2017-09-27(5 rows)
TDSQL PostgreSQL版 支持视图引用表或字段改名联动，不受影响。
postgres=# \\d+ t_view View "tdsql_pg.t_view" Column | Type | Collation | Nullable | Default | Storage | Description ——–+———+———–+———-+———+———-+————- id | integer | | | | plain | mc | text | | | | extended | View definition: SELECT t.id, t.mc FROM t; postgres=# alter table t rename to t_new; ALTER TABLE Time: 62.875 ms postgres=# alter table t_new rename mc to mc_new; ALTER TABLE Time: 22.081 ms postgres=# \\d+ t_view View "tdsql_pg.t_view" Column | Type | Collation | Nullable | Default | Storage | Description ——–+———+———–+———-+———+———-+————- id | integer | | | | plain | mc | text | | | | extended | View definition: SELECT t_new.id, t_new.mc_new AS mc FROM t_new;
二、删除视图
postgres=# drop table t;DROP TABLEpostgres=# create table t (id int,mc text);CREATE TABLEpostgres=# create view t_view as select * from t;CREATE VIEWpostgres=# create view t_view_1 as select * from t_view;CREATE VIEWpostgres=# create view t_view_2 as select * from t_view; CREATE VIEWpostgres=# drop view t_view_2;DROP VIEW
使用 cascade 强制删除依赖对象。
postgres=# drop view t_view; ERROR: cannot drop view t_view because other objects depend on itDETAIL: view t_view_1 depends on view t_viewHINT: Use DROP … CASCADE to drop the dependent objects too.postgres=# drop view t_view cascade;NOTICE: drop cascades to view t_view_1DROP VIEW
三、创建物化视图
postgres=# CREATE MATERIALIZED VIEW t_range_mv AS select f1,f2::date from t_range; SELECT 5
四、访问物化视图
postgres=# select * from t_range_mv; f1 | f2 —-+———— 1 | 2017-09-27 2 | 2017-09-27 2 | 2017-09-27 1 | 2017-09-27 3 | 2017-09-27(5 rows)
五、增量数据刷新
postgres=# insert into t_range(f1,f3) values(5,10);INSERT 0 1postgres=# select * from t_range; f1 | f2 | f3 | f4 —-+—————————-+—–+—- 1 | 2017-09-27 23:17:39.674318 | 1 | 2 | 2017-09-27 23:17:39.674318 | 50 | 5 | 2017-09-27 23:50:51.576173 | 10 | 2 | 2017-09-27 23:17:39.674318 | 110 | 1 | 2017-09-27 23:39:45.841093 | 151 | 3 | 2017-09-27 23:17:39.674318 | 100 | (6 rows)postgres=# select * from t_range_mv ; f1 | f2 —-+———— 1 | 2017-09-27 2 | 2017-09-27 2 | 2017-09-27 1 | 2017-09-27 3 | 2017-09-27(5 rows)postgres=# REFRESH MATERIALIZED VIEW t_range_mv;REFRESH MATERIALIZED VIEWpostgres=# select * from t_range_mv ; f1 | f2 —-+———— 1 | 2017-09-27 2 | 2017-09-27 5 | 2017-09-27 2 | 2017-09-27 1 | 2017-09-27 3 | 2017-09-27(6 rows)
注意：物化视图数据存储在 CN 节点上面，每个 CN 节点各有一份相同的数据。