Overview of System Catalogs and System Views

System catalogs store structured metadata of MogDB. They are the source of information used by MogDB to control system running and are a core component of the database system.

System views provide ways to query the system catalogs and internal database status.

System catalogs and views are visible to either system administrators or all users. Some system catalogs and views have marked the need of administrator permissions, so they are accessible only to administrators.

You can delete and re-create system catalogs, add columns to them, and insert and update values in them, but doing so may make system information inconsistent and cause system faults. Generally, users should not modify system catalogs or system views, or rename their schemas. They are automatically maintained by the system.

NOTICE: Do not add, delete, or modify system catalogs because doing so will result in exceptions or even MogDB unavailability.

Updatable-views Supported

View updatable means that the user can perform INSERT/UPDATE/DELETE operations on the base table corresponding to the view through the view to update the data of the base table.

In order to make the view updatable, the rows in the view must have one-to-one mapping relationship with the data rows in the base table. Specifically, the view cannot be updated when the following content exists in the view:

• Aggregate functions or window functions, such as sum, count, min, max, avg, etc.

• DISTINCT statement

• HAVING, GROUP BY, START WITH, CONNECT BY

• UNION, UNION ALL, INTERSECT, EXCEPT

• There is a subquery in the selected column.

• There are non-updatable views in the FROM clause.

• The table in the FROM clause is referenced in the subquery of the WHERE clause.

• The columns defined in the view are generated by functions or expressions, such as decode(...).

• For JOIN views

  • There must be no less than one base table with a unique key or primary key when DML is executed for a view on multiple jointed tables.
  • Any INSERT, UPDATE or DELETE operation on a view on multiple joined tables can only modify one base table at a time.

Examples

INSERT VIEW

CREATE TABLE IF NOT EXISTS table1(id int, a char(6), b varchar(6), c varchar(6));

INSERT INTO table1 VALUES(1, reverse('123A78'), '123A78', '1sdfBB');
INSERT INTO table1 VALUES(2, reverse('E3gw78'), 'E3g78', 'lkoier');
INSERT INTO table1 VALUES(3, reverse('HHiiw6'), 'Hiw6', '23fdsg');

CREATE VIEW test_view AS SELECT * FROM table1 WHERE id > 1;

INSERT INTO test_view VALUES(4, '2sdafd', '23ata', 'dstsee'); 

SELECT * FROM table1 WHERE id = 4;
 id |   a    |   b   |   c    
----+--------+-------+--------
  4 | 2sdafd | 23ata | dstsee
(1 row)

DROP VIEW IF EXISTS test_view;
DROP TABLE IF EXISTS table1;

UPDATE VIEW

CREATE TABLE IF NOT EXISTS table1(id int, a char(6), b varchar(6), c varchar(6));
INSERT INTO table1 VALUES(1, reverse('123A78'), '123A78', '1sdfBB');
INSERT INTO table1 VALUES(2, reverse('E3gw78'), 'E3g78', 'lkoier');
INSERT INTO table1 VALUES(3, reverse('HHiiw6'), 'Hiw6', '23fdsg');

CREATE VIEW test_view AS SELECT * FROM table1 WHERE id > 1;

UPDATE test_view SET a = '2222' WHERE id = 2;

SELECT * FROM table1;
 id |   a    |   b    |   c    
----+--------+--------+--------
  1 | 87A321 | 123A78 | 1sdfBB
  3 | 6wiiHH | Hiw6   | 23fdsg
  2 | 2222   | E3g78  | lkoier
(3 rows)

DROP VIEW IF EXISTS test_view;
DROP TABLE IF EXISTS table1;

DELETE VIEW

CREATE TABLE IF NOT EXISTS table1(id int, a char(6), b varchar(6), c varchar(6));

INSERT INTO table1 VALUES(1, reverse('123A78'), '123A78', '1sdfBB');
INSERT INTO table1 VALUES(2, reverse('E3gw78'), 'E3g78', 'lkoier');
INSERT INTO table1 VALUES(3, reverse('HHiiw6'), 'Hiw6', '23fdsg');

CREATE VIEW test_view AS SELECT * FROM table1 WHERE id > 1;

DELETE FROM test_view WHERE id = 2; 

SELECT * FROM table1 WHERE id > 1;
 id |   a    |  b   |   c    
----+--------+------+--------
  3 | 6wiiHH | Hiw6 | 23fdsg
(1 row)

DROP VIEW IF EXISTS test_view;
DROP TABLE IF EXISTS table1;