openGauss每日一练第11天 | 视图

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

--为系统表PG_DATABASE创建视图
CREATE VIEW db_view AS SELECT * FROM pg_database;

--重命名视图并修改owner为jim;
ALTER VIEW db_view RENAME TO db_view_v2;

--修改视图owner为jim;
create user jim password 'abcd_123';
alter view db_view_v2 owner to jim;

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

\c - jim
CREATE TABLE student(sid varchar(6), sname varchar(10), sage date, ssex varchar(10));
CREATE VIEW student_view AS SELECT * FROM student;

ALTER VIEW jim.student_view SET SCHEMA public;

3. 使用pg_views查看视图信息

select * from pg_views where viewname = 'db_view_v2' or viewname = 'student_view';

4. 删除视图、表、用户

DROP VIEW db_view_v2;
DROP VIEW student_view;
DROP TABLE jim.student;
DROP USER jim;