openGauss每日一练第 11 天 |视图

学习openGauss视图

视图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。

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

create database jim;
create user jim identified by 'Abcd_123';
create view my_database as select datname,datdba,dattablespace from pg_database where datname='jim';
alter view my_database owner to jim;
select * from my_database;
复制

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

\c jim
create schema xhy;
create table student (id int, name varchar(20), sex char(1));
create view student_name as select name from student;
alter view student_name set schema xhy;
复制

3. 使用pg_views查看视图信息

select * from pg_views where viewname='my_database';
复制

4. 删除视图、表、用户

drop view student_name;
drop table student;
drop user jim;
复制

因为视图student_name依赖于表student，直接删除表报错，可添加cascade关键字连同表上的视图一起删除，或者先删除视图再删除表
jim=# drop table student;
ERROR:  cannot drop table student because other objects depend on it
DETAIL:  view xhy.student_name depends on table student
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
1) 如下提示视图xhy.student_name被删除；
jim=# drop table student cascade;
NOTICE:  drop cascades to view xhy.student_name
DROP TABLE
2）先删除视图，再删除表
jim=# drop view student_name;
DROP VIEW
jim=# drop table student;
DROP TABLE
# 如下提示有2个对象在用户jim上
jim=# drop user jim;
ERROR:  role "jim" cannot be dropped because some objects depend on it
DETAIL:  2 objects in database omm
# 删除该用户的数据库jim，当前数据库下不能删除本数据库
jim=# drop database jim;
ERROR:  cannot drop the currently open database
jim=# \c omm;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# drop database jim;
DROP DATABASE
# 提示还有视图 my_database，继续删除视图my_database
omm=# drop user jim;
ERROR:  role "jim" cannot be dropped because some objects depend on it
DETAIL:  owner of view my_database
omm=# drop view my_database;
DROP VIEW
#再次删除jim用户，删除成功
omm=# drop user jim;
DROP ROLE
复制