学习openGauss视图,课后做业打卡；
视图与基本表不同，是一个虚拟的表;数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中;

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

Create schema mydb;
--创建视图
CREATE VIEW mydb.v_pg_database AS SELECT * FROM pg_database;
SELECT * FROM mydb.v_pg_database ;
--重命名视图
ALTER VIEW mydb.v_pg_database RENAME TO v_pg_database_01;
--修改owner
create user jim password 'abcd@123';
alter view mydb.v_pg_database_01 owner to jim;

--pg_views查看视图信息
omm=# select * from pg_views where viewname = 'v_pg_database_01';
 schemaname |     viewname     | viewowner |         definition          
------------+------------------+-----------+-----------------------------
 mydb       | v_pg_database_01 | jim       | SELECT  * FROM pg_database;
(1 row)

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

--创建表
CREATE TABLE mydb.student
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;
INSERT INTO mydb.student VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
--创建视图
create view mydb.v_student as select * from mydb.student;
--修改schema
ALTER VIEW mydb.v_student SET schema public;
--查看视图信息
select * from pg_views where viewname = 'v_student';

3.使用pg_views查看视图信息

omm=# select * from pg_views where viewname = 'v_pg_database_01' or viewname = 'v_student';
 schemaname |     viewname     | viewowner |          definition          
------------+------------------+-----------+------------------------------
 mydb       | v_pg_database_01 | jim       | SELECT  * FROM pg_database;
 public     | v_student        | omm       | SELECT  * FROM mydb.student;
(2 rows)

4.删除视图、表、用户

先删除视图，再删除视图的基表，否则会报依赖错误；

DROP VIEW mydb.v_pg_database_01;
Drop view v_student;
drop table mydb.student;
drop schema mydb;
drop user jim;