openGauss每日一练第 11 天 |openGauss视图

学习openGauss的第十一天。

主要内容是openGauss视图,创建删除视图，更改视图属性

连接数据库

root@modb:~# su - omm

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.

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

omm=# create view view_test as

omm-# select * from pg_database ;

CREATE VIEW

omm=# create user jim password 'test_123' ;

NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.

CREATE ROLE

omm=# alter view view_test owner to jim ;

ALTER VIEW

omm=# Alter view view_test rename to view_test1 ;

ALTER VIEW

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

omm=# create table jim.student(id int , name varchar(20)) ;

CREATE TABLE

omm=# create view jim.v_s as select * from jim.student ;

CREATE VIEW

omm=# create schema test ;

CREATE SCHEMA

omm=# Alter view jim.v_s  set schema test ;

ALTER VIEW

3.使用pg_views查看视图信息

omm=# select * from pg_views where viewowner='jim'  ;

 schemaname |  viewname  | viewowner |         definition         

------------+------------+-----------+-----------------------------

 public     | view_test1 | jim       | SELECT  * FROM pg_database;

 test       | v_s        | jim       | SELECT  * FROM jim.student;

(2 rows)

4.删除视图、表、用户

omm=# drop view view_test1 ;

DROP VIEW

omm=# drop view test.v_s ;

DROP VIEW

omm=# drop table jim.student ;

DROP TABLE

omm=# drop schema test ;

omm=# DROP SCHEMA

omm=#

omm=# drop user jim cascade ;

DROP ROLE