opengauss学习的第11天

#opengauss的第11天

1.为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim
omm=# create user jim sysadmin password ‘root123.COM’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# create view pgdatabase_view as select * from pg_database;
CREATE VIEW
omm=#
omm=#
omm=# \dv
List of relations
Schema | Name | Type | Owner | Storage
--------±----------------±-----±------±--------
public | pgdatabase_view | view | omm |

omm=# create user jim sysadmin password ‘root123.COM’;
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE

omm=# alter view pgdatabase_view owner to jim;
ALTER VIEW
omm=#
omm=# \dv
List of relations
Schema | Name | Type | Owner | Storage
--------±----------------±-----±------±--------
public | pgdatabase_view | view | jim |

2.创建一个用户表student，并在用户表上创建视图，修改视图schema
omm=# create table student(id bigint,name char(40));
CREATE TABLE
omm=# create view v_student as select * from student;
CREATE VIEW
omm=# create schema sch1;
CREATE SCHEMA

omm=# \dv
List of relations
Schema | Name | Type | Owner | Storage
--------±----------------±-----±------±--------
public | pgdatabase_view | view | jim |
public | student_view | view | omm |

omm=# \dv
List of relations
Schema | Name | Type | Owner | Storage
--------±-------------±-----±------±--------
sch1 | v_student | view | omm |

3.使用pg_views查看视图信息
omm=# select * from pg_views where viewname in(‘pgdatabase_view’,‘v_student’);
schemaname | viewname | viewowner | definition
------------±----------------±----------±-------------------------------
public | pgdatabase_view | jim | SELECT * FROM pg_database;
sch1 | v_student | omm | SELECT * FROM public.student;
(2 rows)

4.删除视图、表、用户

omm=# drop user jim;
ERROR: role “jim” cannot be dropped because some objects depend on it
DETAIL: owner of view public.pgdatabase_view
omm=# drop view public.pgdatabase_view;
DROP VIEW
omm=# drop schema sch1;
ERROR: cannot drop schema sch1 because other objects depend on it
DETAIL: view v_student depends on schema sch1
view student_view depends on schema sch1
HINT: Use DROP … CASCADE to drop the dependent objects too.
omm=# drop schema sch1 cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to view v_student
drop cascades to view student_view