openGauss

学习内容

–为系统表pg_tablespace创建字段spcname为pg_default组成的视图
CREATE VIEW tps_view AS
SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
–查看视图

omm=# \d pg_tablespace;
  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers 
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] | 
 spcoptions | text[]    | 
 spcmaxsize | text      | 
 relative   | boolean   | 
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid) TABLESPACE pg_global, tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname) TABLESPACE pg_global, tablespace "pg_global"
Replica Identity: NOTHING
Tablespace: "pg_global"

作业内容

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

create view view_tem as select * from pg_database where datname = 'dat_default';
alter view view_tem rename to view_new;
create user jim identified by 'adgh*(^687';
alter view view_new owner to jim;

omm=# create view view_tem as select * from pg_database where datname = 'dat_default';
CREATE VIEW
omm=# alter view view_tem rename to view_new;
ALTER VIEW
omm=# 
omm=# create user jim identified by 'adgh*(^687';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view view_new owner to jim;
ALTER VIEW
omm=# 

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

create table student(
id int, name char(30)
);

create view stu_view as select * from student where name = 'a';
create schema tpcds1;
alter view stu_view set schema tpcds1;

omm=# create table student(
omm(# id int, name char(30)
omm(# );
CREATE TABLE
omm=# create view schema as select * from student where name = 'a';
CREATE VIEW
omm=# create view stu_view as select * from student where name = 'a';
CREATE VIEW
omm=# create schema tpcds1;
CREATE SCHEMA
omm=# alter view stu_view set schema tpcds1;
ALTER VIEW
omm=# 

3.使用pg_views查看视图信息

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

omm=# select * from pg_views where schemaname = 'tpcds1';
 schemaname | viewname | viewowner |                         definition                         
------------+----------+-----------+------------------------------------------------------------
 tpcds1     | stu_view | omm       | SELECT  * FROM student WHERE (student.name = 'a'::bpchar);
(1 row)

omm=# 
omm=# select * from pg_views where schemaname = 'tpcds1' or schemaname = 'public';
 schemaname | viewname | viewowner |                                    definition                                    
------------+----------+-----------+----------------------------------------------------------------------------------
 public     | tps_view | omm       | SELECT  * FROM pg_tablespace WHERE (pg_tablespace.spcname = 'pg_default'::name);
 public     | view_new | jim       | SELECT  * FROM pg_database WHERE (pg_database.datname = 'dat_default'::name);
 public     | schema   | omm       | SELECT  * FROM student WHERE (student.name = 'a'::bpchar);
 tpcds1     | stu_view | omm       | SELECT  * FROM student WHERE (student.name = 'a'::bpchar);
(4 rows)

omm=# 

4.删除视图、表、用户

drop view schema;
drop view view_new;
drop view stu_view;
drop view tps_view;

drop table student;

drop user jim (cascade);

omm=# drop view schema;
DROP VIEW
omm=# drop view view_new;
DROP VIEW
omm=# drop view stu_view;
ERROR:  view "stu_view" does not exist
omm=# drop view tps_view;
DROP VIEW
omm=# 
omm=# drop table student;
ERROR:  cannot drop table student because other objects depend on it
DETAIL:  view tpcds1.stu_view depends on table student
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# 
omm=# drop user jim;
DROP ROLE
omm=# drop table student;
ERROR:  cannot drop table student because other objects depend on it
DETAIL:  view tpcds1.stu_view depends on table student
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
omm=# drop table student cascade;
NOTICE:  drop cascades to view tpcds1.stu_view
DROP TABLE