openGauss每日一练第11天 |学习openGauss视图
1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,
CREATE VIEW tps_view AS
SELECT * FROM PG_DATABASE WHERE spcname = ‘pg_default’;
SELECT * FROM tps_view ;
create user jim password ‘abcd@123’;
alter view tps_view owner to jim;
2.创建一个用户表student，并在用户表上创建视图，修改视图schema;
Create schema tpcds;
CREATE TABLE tpcds.student
( c_customer_sk integer,
c_customer_id char(5),
c_first_name char(6),
c_last_name char(8)
) ;
INSERT INTO tpcds.student VALUES
(6885, 1, ‘Joes’, ‘Hunter’),
(4321, 2, ‘Lily’,‘Carter’),
(9527, 3, ‘James’, ‘Cook’),
(9500, 4, ‘Lucy’, ‘Baker’);
CREATE VIEW tpcds.student_view_v2 AS
SELECT * FROM tpcds.customer
WHERE c_customer_sk < 6000;
ALTER VIEW tpcds.student_view_v2 SET schema public;
3.使用pg_views查看视图信息
select * from pg_views where viewname = ‘student_view_v2’ or viewname = ‘tps_view’;
4.删除视图、表、用户
DROP VIEW student_view_v2;
Drop view tps_view;
drop table tpcds.student;
drop user jim ;
