创建视图示例
最近更新时间: 2025-02-18 16:02:00
postgres=# create view t_range_view as select * from t_range;
CREATE VIEW
postgres=# select * from t_range_view;
f1| f2 | f3 | f4
----+----------------------------+-----+----
1 |2017-09-27 23:17:39.674318 | 1 |
2 |2017-09-27 23:17:39.674318 | 50 |
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 |
(5 rows)- 数据类型重定义。
postgres=# create view t_range_view as select f1,f2::date from t_range;
CREATE VIEW
postgres=# 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 VIEW
postgres=# 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)- tbase支持视图引用表或字段改名联动，不受影响。
postgres=# \d+ t_view
View"tbase.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 tomc_new;
ALTER TABLE
Time: 22.081 ms
postgres=# \d+ t_view
View"tbase.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;