openGauss每日一练第18天 | openGauss逻辑结构：视图管理

1.创建表，创建普通视图

## 创建表customeromm=#create table customer (
omm(# id integer,
omm(# name varchar(10),
omm(# age integer default 10,
omm(# primary key (id)
omm(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customer_pkey" for table "customer"
CREATE TABLE## 插入数据omm=# insert into customer values (1,'li',20);
INSERT 0 1
omm=# insert into customer values 
omm-# (2,'wang',21),
omm-# (3,'liu',22),
omm-# (4,'yu',23);
INSERT 0 3
##创建索引omm=# create view customer_view as
omm-# select * from customer 
omm-# where age > 20;
CREATE VIEW
omm=# select * from customer_view ;
 id | name | age 
----+------+-----
  2 | wang |  21
  3 | liu  |  22
  4 | yu   |  23
(3 rows)

2.使用视图创建新的视图

omm=# create view customer_view_new as
omm-# select * from customer_view 
omm-# where id = 3;
CREATE VIEW
omm=# select * from customer_view_new ;
 id | name | age 
----+------+-----
  3 | liu  |  22
(1 row)

3.创建物化视图

omm=# create materialized view mv_customer_view as
omm-# select * from customer
omm-# where age > 22;
CREATE MATERIALIZED VIEW
omm=# select * from mv_customer_view ;
 id | name | age 
----+------+-----
  4 | yu   |  23
(1 row)

4.手动更新物化视图

## 向表中插入新数据omm=# insert into customer values (5,'lv',24);
INSERT 0 1
omm=# select * from mv_customer_view ;
 id | name | age 
----+------+-----
  4 | yu   |  23
(1 row)
## 更新物化视图
omm=# refresh materialized view mv_customer_view ;
REFRESH MATERIALIZED VIEW
omm=# select * from mv_customer_view ;
 id | name | age 
----+------+-----
  4 | yu   |  23
  5 | lv   |  24
(2 rows)

5.删除创建的视图

omm=# drop view customer_view;
ERROR:  cannot drop view customer_view because other objects depend on it
DETAIL:  view customer_view_new depends on view customer_view
HINT:  Use DROP ... CASCADE to drop the dependent objects too.## customer_view视图被customer_view_new引用，所以删除会有报错，可添加cascade参数强制删除
omm=# drop view customer_view_new ;
DROP VIEW
omm=# drop view customer_view;
DROP VIEW
omm=# drop materialized view mv_customer_view ;
DROP MATERIALIZED VIEW