创建和删除视图

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)

 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 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

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 

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 1

postgres=# 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 VIEW
postgres=# 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)