(openGauss每日一练第 11天)：openGauss视图创建、查看、修改、删除

1.创建视图

--1、使用gsql登录openGauss\
root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=#

--2、为系统表pg_tablespace创建字段spcname为pg_default组成的视图
omm=# CREATE VIEW tps_view AS SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
CREATE VIEW

--3、查看视图
omm=# SELECT * FROM tps_view ;
  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative 
------------+----------+--------+------------+------------+----------
 pg_default |       10 |        |            |            | f
(1 row)

--4、创建一个由c_customer_sk小于6000的内容组成的视图
omm=# Create schema tpcds;
CREATE SCHEMA

omm=# CREATE TABLE tpcds.customer
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;
CREATE TABLE

omm=# INSERT INTO tpcds.customer VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
INSERT 0 4

omm=# CREATE VIEW tpcds.customer_details_view_v1 AS
SELECT * FROM tpcds.customer
WHERE c_customer_sk < 6000;
CREATE VIEW

--5、查看视图
omm=# select * from tpcds.customer_details_view_v1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          4321 | 2             | Lily         | Carter  
(1 row)

--6、系统视图
omm=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
 schemaname |         viewname         | viewowner |                                    definition                                    
------------+--------------------------+-----------+----------------------------------------------------------------------------------
 public     | tps_view                 | omm       | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name);
 tpcds      | customer_details_view_v1 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 6000);
(2 rows)

2.修改视图定义

--1、修改视图名称
omm=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
ALTER VIEW

--2、修改视图所属schema
omm=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
ALTER VIEW

--3、修改视图owner
--先创建用户
omm=# create user jack password 'abcd@123';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

--修改视图owner
omm=# alter view tps_view owner to jack;
ALTER VIEW

--4、查看系统视图tps_views
omm=# select * from pg_views where viewname = 'customer_details_view_v2' or viewname = 'tps_view';
 schemaname |         viewname         | viewowner |                                    definition                                    
------------+--------------------------+-----------+----------------------------------------------------------------------------------
 public     | tps_view                 | jack      | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name);
 public     | customer_details_view_v2 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 6000);
(2 rows)

3.删除视图

omm=# DROP VIEW customer_details_view_v2;
DROP VIEW

omm=# Drop view tps_view;
DROP VIEW

练习：

1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,

omm=# create view ts_view as select * from PG_DATABASE;
CREATE VIEW

omm=# create user jim password 'abc_1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# alter view ts_view owner to jim;
ALTER VIEW

2.创建一个用户表student，并在用户表上创建视图，修改视图schema;

omm=# create table school(id int,name varchar(20));
omm=# CREATE TABLE

omm=# insert into school values(1,'xiaolan');
INSERT 0 1

omm=# create view school_view as select * from school;
CREATE VIEW

omm=# create schema schema_01;
CREATE SCHEMA

omm=# alter view public.school_view set schema schema_01;
ALTER VIEW

3.使用pg_views查看视图信息

omm=# select * from pg_views where viewname = 'ts_view' or viewname = 'public ';
 schemaname | viewname | viewowner |         definition          
------------+----------+-----------+-----------------------------
 public     | ts_view  | jim       | SELECT  * FROM pg_database;
(1 row)

4.删除视图、表、用户

omm=# drop view ts_view;
DROP VIEW

omm=# drop view schema_01.school_view;
DROP VIEW
omm=#

omm=# drop table school;
DROP TABLE

drop user jim;omm=# 
DROP ROLE