openGauss每日一练第11天，视图

openGauss每日一练第11天课后作业

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

create view db_view as select * from pg_database;

alter view db_view rename to db_view2;

create user jim password 'gauss_4U';

alter view db_view2 owner to jim;

select * from pg_views where viewname='db_view2';

\dv

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

create table student (

id int,

first_name char(20),

last_name char(20)

);

insert into student values

(1, 'Joes', 'Hunter'),

(2, 'Lily','Carter'),

(3, 'James', 'Cook'),

(4, 'Lucy', 'Baker');

create view student_view as select * from student where id>2;

create schema school;

select * from student_view;

alter view student set schema school;

\dv

\dv school.*

3.使用pg_views查看视图信息

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

4.删除视图、表、用户

drop view db_view2;

drop view school.student_view;

drop table student;

drop user jim cascade;

drop schema school;