root@modb:~# su - omm
omm@modb:~$ gsql -r
failed to connect Unknown:5432.
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.
omm=# CREATE VIEW tps_view AS
omm-# SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
CREATE VIEW
omm=# SELECT * FROM tps_view ;
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
------------+----------+--------+------------+------------+----------
pg_default | 10 | | | | f
(1 row)
omm=# Create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.customer
omm-# ( c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8)
omm(# ) ;
CREATE TABLE
omm=# INSERT INTO tpcds.customer VALUES
omm-# (6885, 1, 'Joes', 'Hunter'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
omm=# CREATE VIEW tpcds.customer_details_view_v1 AS
omm-# SELECT * FROM tpcds.customer
omm-# WHERE c_customer_sk < 6000;
CREATE VIEW
omm=# 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)
omm=# select * from pg_views where schemaname = 'tpcds' or schemaname = 'public';
schemaname | viewname | viewowner | definition
------------+--------------------------+-----------+-------------------------------------------------
---------------------------------
public | tps_view | omm | SELECT * FROM pg_tablespace WHERE (pg_tablespac
e.spcname = 'pg_default'::name);
tpcds | customer_details_view_v1 | omm | SELECT * FROM tpcds.customer WHERE (customer.c_
customer_sk < 6000);
(2 rows)
omm=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;
ALTER VIEW
omm=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;
ALTER VIEW
omm=# create user jack password 'abcd@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# alter view tps_view owner to jack;
ALTER VIEW
omm=# 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_tablespac
e.spcname = 'pg_default'::name);
public | customer_details_view_v2 | omm | SELECT * FROM tpcds.customer WHERE (customer.c_
customer_sk < 6000);
(2 rows)
omm=# DROP VIEW customer_details_view_v2;
DROP VIEW
omm=# Drop view tps_view;
DROP VIEW
omm=# CREATE VIEW olabll_view AS SELECT * FROM PG_DATABASE;
WARNING: Session unused timeout.
FATAL: terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
重连
omm=# \c
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# CREATE VIEW olabll_view AS SELECT * FROM PG_DATABASE;
CREATE VIEW
omm=# select * from olabll_view;
datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnli
mit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility | datacl | datfro
zenxid64
-----------+--------+----------+-------------+-------------+---------------+--------------+----------
----+---------------+--------------+---------------+------------------+----------------------+-------
---------
template1 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | t | t |
-1 | 14176 | 0 | 1663 | PG | {=c/omm,omm=CTc/omm} |
8176
omm | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t |
-1 | 14176 | 7891 | 1663 | PG | |
7891
template0 | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | t | f |
-1 | 14176 | 0 | 1663 | PG | {=c/omm,omm=CTc/omm} |
7891
postgres | 10 | 7 | en_US.UTF-8 | en_US.UTF-8 | f | t |
-1 | 14176 | 0 | 1663 | PG | |
8461
(4 rows)
omm=# create schema jim;
CREATE SCHEMA
omm=# ALTER VIEW olabll_view RENAME TO olabll_view_2;
ALTER VIEW
omm=# select * from pg_views where viewname = 'olabll_view_2';
schemaname | viewname | viewowner | definition
------------+---------------+-----------+-----------------------------
public | olabll_view_2 | omm | SELECT * FROM pg_database;
(1 row)
omm=# ALTER VIEW olabll_view_2 SET schema jim;
ALTER VIEW
omm=# \dS+ jim.olabll_view_2
View "jim.olabll_view_2"
Column | Type | Modifiers | Storage | Description
------------------+-----------+-----------+----------+-------------
datname | name | | plain |
datdba | oid | | plain |
encoding | integer | | plain |
datcollate | name | | plain |
datctype | name | | plain |
datistemplate | boolean | | plain |
datallowconn | boolean | | plain |
datconnlimit | integer | | plain |
datlastsysoid | oid | | plain |
datfrozenxid | xid32 | | plain |
dattablespace | oid | | plain |
datcompatibility | name | | plain |
datacl | aclitem[] | | extended |
FROM pg_database;
datfrozenxid64 | xid | | plain |
View definition:
SELECT *
omm=# create user olabll password 'gauss_123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
omm=# create table olabll.student(id int not null,stu_name char(10));
CREATE TABLE
omm=# insert into olabll.student values (1,'aa');
INSERT 0 1
omm=# insert into olabll.student values (2,'bb');
omm=# INSERT 0 1
omm=# create view olabll.bb_view as select * from olabll.student where stu_name='bb';
CREATE VIEW
omm=# select *from olabll.bb_view;
id | stu_name
----+------------
2 | bb
(1 row)
omm=# alter view olabll.bb_view set schema jim;
ALTER VIEW
omm=# alter view jim.bb_view set schema public;
ALTER VIEW
omm=# select * from pg_views where viewname = 'bb_view' or viewname = 'olabll_view_2';
schemaname | viewname | viewowner | definition
------------+---------------+-----------+------------------------------------------------------------
------------
jim | olabll_view_2 | omm | SELECT * FROM pg_database;
public | bb_view | olabll | SELECT * FROM olabll.student WHERE (student.stu_name = 'bb
'::bpchar);
(2 rows)
omm=# \d public.bb_view;
View "public.bb_view"
Column | Type | Modifiers
----------+---------------+-----------
id | integer |
stu_name | character(10) |
omm=# DROP VIEW jim.olabll_view_2;
DROP VIEW
omm=# drop view public.bb_view;
DROP VIEW
omm=# drop table olabll.student;
DROP TABLE
omm=# drop user olabll;
omm=# DROP ROLE
omm=# drop schema jim;
DROP SCHEMA
omm=#
pg可以创建临时视图,创建完后，在别的会话中是无法查看该视图的，这个会话退出后，这个视图的定义也被清除掉了
omm=# create temp view olab_tmp_view as select * from pg_database;
CREATE VIEW
omm=# \d+ olab_tmp_view
View "pg_temp_gaussdb_2_1_281471970861664.olab_tmp_view"
Column | Type | Modifiers | Storage | Description
------------------+-----------+-----------+----------+-------------
datname | name | | plain |
datdba | oid | | plain |
encoding | integer | | plain |
datcollate | name | | plain |
datctype | name | | plain |
datistemplate | boolean | | plain |
datallowconn | boolean | | plain |
datconnlimit | integer | | plain |
datlastsysoid | oid | | plain |
datfrozenxid | xid32 | | plain |
dattablespace | oid | | plain |
datcompatibility | name | | plain |
datacl | aclitem[] | | extended |
datfrozenxid64 | xid | | plain |
View definition:
SELECT *
FROM pg_database;
omm=# \c
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "omm" as user "omm".
omm=# \d+ olab_tmp_view
Did not find any relation named "olab_tmp_view".
可更新视图
omm=# create table t1_olab as select * from pg_class;
INSERT 0 704
omm=# select * from t1_olab where rownum<2;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace |
relpages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx |
relcudescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | re
lchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhas
clusterkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident
| relfrozenxid64 | relbucket | relbucketkey
--------------+--------------+---------+-----------+----------+-------+-------------+---------------+
----------+-----------+---------------+---------------+---------------+---------------+-------------+
----------------+--------------+-------------+-------------+----------------+---------+----------+---
--------+------------+------------+-------------+----------------+----------------+----------+-------
-----------+----------------+----------+--------------+-------------------+------------+-------------
-+----------------+-----------+--------------
pg_statistic | 11 | 11334 | 0 | 10 | 0 | 13689 | 0 |
18 | 476 | 18 | 2840 | 0 | 0 | 0 |
0 | 0 | t | f | p | r | 29 |
0 | f | f | f | f | f | 0 | f
| f | n | 0 | {omm=arwdDxt/omm} | | n
| 7891 | |
(1 row)
omm=# create view olab_view as select * from t1_olab;
CREATE VIEW
需要创建规则来使视图可更新
omm=# create or replace rule r_olab_update as on update to olab_view do instead update t1_olab set relname=new.relname where relnamespace=new.relnamespace;
CREATE RULE
omm=# create rule r_olab_del as on delete to olab_view do instead delete from t1_olab where id=old.id;
ERROR: column "id" does not exist
LINE 1: ...to olab_view do instead delete from t1_olab where id=old.id;
^
omm=# create rule r_olab_del as on delete to olab_view do instead delete from t1_olab where relname=old.relname;
CREATE RULE
同理插入，删除都需要创建规则
omm=# delete from olab_view where relname is not null;
DELETE 704
omm=# select *from olab_view;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relp
ages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relc
udescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchec
ks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclust
erkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid6
4 | relbucket | relbucketkey
---------+--------------+---------+-----------+----------+-------+-------------+---------------+-----
-----+-----------+---------------+---------------+---------------+---------------+-------------+-----
-----------+--------------+-------------+-------------+----------------+---------+----------+--------
---+------------+------------+-------------+----------------+----------------+----------+------------
------+----------------+----------+--------------+--------+------------+--------------+--------------
--+-----------+--------------
(0 rows)
omm=# select * from t1_olab;
relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relp
ages | reltuples | relallvisible | reltoastrelid | reltoastidxid | reldeltarelid | reldeltaidx | relc
udescrelid | relcudescidx | relhasindex | relisshared | relpersistence | relkind | relnatts | relchec
ks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relcmprs | relhasclust
erkey | relrowmovement | parttype | relfrozenxid | relacl | reloptions | relreplident | relfrozenxid6
4 | relbucket | relbucketkey
---------+--------------+---------+-----------+----------+-------+-------------+---------------+-----
-----+-----------+---------------+---------------+---------------+---------------+-------------+-----
-----------+--------------+-------------+-------------+----------------+---------+----------+--------
---+------------+------------+-------------+----------------+----------------+----------+------------
------+----------------+----------+--------------+--------+------------+--------------+--------------
--+-----------+--------------
(0 rows)
omm=#
