openGauss每日一练第11天 视图

1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
omm=# create view v_pg_database as select * from pg_database;
CREATE VIEW
omm=# create user jim password "abcd@123';
omm"#
omm"# alter view v_pg_database owner to jim;
omm=# select * from pg_views where viewname='v_pg_database';
schemaname | viewname | viewowner | definition
------------+---------------+-----------+-----------------------------
public | v_pg_database | jim | SELECT * FROM pg_database;
(1 row)

2.创建一个用户表student，并在用户表上创建视图，修改视图schema;
omm=# CREATE TABLE tpcds.student
omm-# ( c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8)
omm(# ) ;
CREATE TABLE
omm=# INSERT INTO tpcds.student VALUES
omm-# omm-# (6885, 1, 'Joes', 'Hunter'),
(4321, 2, 'Lily','Carter'),
omm-# omm-# (9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
INSERT 0 4
omm=# CREATE VIEW tpcds.student_details_view_v1 AS
omm-# SELECT * FROM tpcds.student
omm-# WHERE c_customer_sk < 6000;
CREATE VIEW
omm=#
omm=# ALTER VIEW tpcds.student_details_view_v1 SET schema public;
ALTER VIEW

3.使用pg_views查看视图信息
omm=# select * from pg_views where viewname='student_details_view_v1';
schemaname | viewname | viewowner | definition
------------+-------------------------+-----------+--------------------------------------------------------------------
public | student_details_view_v1 | omm | SELECT * FROM tpcds.student WHERE (student.c_customer_sk < 6000);
(1 row)

4.删除视图、表、用户
DROP VIEW student_details_view_v1;
Drop view v_pg_database;
drop user jim;
drop schema tpcds;