openGauss每日一练第11天 | 学习openGauss视图

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

CREATE VIEW database_view AS SELECT * FROM PG_DATABASE;

ALTER VIEW database_view RENAME TO database_view1;

create user jim password 'abcd@1234';
alter view database_view1 owner to jim;

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

CREATE TABLE student (
student_id VARCHAR (10) PRIMARY KEY NOT NULL,
student_name VARCHAR (20) NOT NULL,
student_sex VARCHAR (2),
student_age INTEGER (3),
dept_id VARCHAR (2),
class_id VARCHAR (8)
);

INSERT INTO student VALUES
('6885', 'Joes', 'M', 8, '3','2'),
('4321', 'Lily', 'F', 9, '2','2'),
('9527', 'James', 'M', 8, '4','1'),
('9500', 'Lucy', 'F', 9, '3','3');


CREATE VIEW student_view_v1 AS SELECT * FROM student WHERE student_age > 8;

ALTER VIEW student_view_v1 SET schema tpcds;

3.使用pg_views查看视图信息

select * from pg_views where viewname = 'student_view_v1' or viewname = 'database_view1';

4.删除视图、表、用户

DROP VIEW database_view1;
drop view tpcds.student_view_v1;
drop table student;
drop user jim;