创建和删除视图

最近更新时间:2019-12-03 17:11:08

创建视图

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 to mc_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;

删除视图

postgres=# drop table t;
DROP TABLE

postgres=# create table t (id int,mc text);
CREATE TABLE
postgres=# create view t_view as select * from t;
CREATE VIEW
postgres=# create view t_view_1 as select * from t_view;
CREATE VIEW
postgres=# create view t_view_2 as select * from t_view; 
CREATE VIEW

postgres=# drop view t_view_2;
DROP VIEW

\#使用cascade强制删除依赖对象

postgres=# drop view t_view;  
ERROR:  cannot drop view t_view because other objects depend on it
DETAIL:  view t_view_1 depends on view t_view
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop view t_view cascade;
NOTICE:  drop cascades to view t_view_1
DROP VIEW