openGauss每日一练第11天|视图的创建、修改与删除操作

视图（view）有点类似于C++的StringView的概念，不是copy，只提供view的功能，真实的数据依旧存在原有的表中。

课程学习

1.创建视图

• 为系统表pg_tablespace创建字段spcname为pg_default组成的视图

CREATE VIEW tps_view AS
SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';

  spcname   | spcowner | spcacl | spcoptions | spcmaxsize | relative 
------------+----------+--------+------------+------------+----------
 pg_default |       10 |        |            |            | f
(1 row)
	

• 查看视图

SELECT * FROM tps_view ;

• 创建一个由c_customer_sk小于6000的内容组成的视图

Create schema tpcds;
CREATE TABLE tpcds.customer
(  c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;
INSERT INTO tpcds.customer VALUES    
(6885, 1, 'Joes', 'Hunter'),    
(4321, 2, 'Lily','Carter'),    
(9527, 3, 'James', 'Cook'),
(9500, 4, 'Lucy', 'Baker');
CREATE VIEW tpcds.customer_details_view_v1 AS
SELECT * FROM tpcds.customer
WHERE c_customer_sk < 6000;

• 查看视图

select * from tpcds.customer_details_view_v1;
 c_customer_sk | c_customer_id | c_first_name | c_last_name 
---------------+---------------+--------------+-------------
          4321 | 2             | Lily         | Carter  
(1 row)

• 系统视图

select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';

 schemaname |         viewname         | viewowner |                                    definition                  
                  
------------+--------------------------+-----------+----------------------------------------------------------------
------------------
 public     | tps_view                 | omm       | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg
_default'::name);
 tpcds      | customer_details_view_v1 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 6
000);
(2 rows)

2.修改视图定义

• 修改视图名称

ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

• 修改视图所属schema

ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

• 修改视图owner

create user jack password 'abcd@123';
alter view tps_view owner to jack;

• 查看系统视图tps_views

select * from pg_views where viewname = 'customer_details_view_v2' or viewname = 'tps_view';

 schemaname |         viewname         | viewowner |                                    definition                  
                  
------------+--------------------------+-----------+----------------------------------------------------------------
------------------
 public     | tps_view                 | jack      | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg
_default'::name);
 public     | customer_details_view_v2 | omm       | SELECT  * FROM tpcds.customer WHERE (customer.c_customer_sk < 6
000);
(2 rows)

3.删除视图

DROP VIEW customer_details_view_v2;
drop view tps_view;

课程作业

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

create view pg_database_view1 as select * from pg_database;
alter view pg_database_view1 rename to pg_database_view2;
create user jim password 'abcd@456';
alter view pg_database_view2 owner to jim;

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

create schema tpcds;
create table tpcds.student
(
id integer not null,
name varchar(30) not null
);
insert into tpcds.student values
(6885, 'Joes'),    
(4321, 'Lily'),    
(9527, 'James'),
(9500, 'Lucy');

create view tpcds.student_view1 as select * from tpcds.student;
alter view tpcds.student_view1 set schema public;

3.使用pg_views查看视图信息

omm=# select * from pg_views where viewname = 'student_view1' or viewname = 'pg_database_view2';
 schemaname |     viewname      | viewowner |          definition           
------------+-------------------+-----------+-------------------------------
 public     | pg_database_view2 | jim       | SELECT  * FROM pg_database;
 public     | student_view1     | omm       | SELECT  * FROM tpcds.student;
(2 rows)

4.删除视图、表、用户

drop view pg_database_view2; 
drop user jim;
drop view student_view1 ;
drop schema tpcds cascade ;