openGauss每日一练第11天 | 学习心得体会

视图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。

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

create view pg_db_v as select * from pg_database;
alter view pg_db_v rename to pg_db_v2;
create user jim identified by 'test_123';
alter view pg_db_v2 owner to jim;

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

create table student (id int, name char(30), age int);
insert into student values(1,'a',15);
insert into student values(2,'b',14);
insert into student values(3,'c',16);
insert into student values(4,'d',14);
insert into student values(5,'e',15);
create view student2 as select * from student where age < 15;
alter view student2 set schema jim;

3.使用pg_views查看视图信息

omm=# select * from pg_views where viewname='student2';
 schemaname | viewname | viewowner |                    definition                    
------------+----------+-----------+--------------------------------------------------
 jim        | student2 | omm       | SELECT  * FROM student WHERE (student.age < 15);
(1 row)

4.删除视图、表、用户

drop view pg_db_v2;
drop view jim.student2;
drop table student;
drop user jim;