5.37. PostgreSQL View (View)

The View (view) is a fake table, just a PostgreSQL statement stored in the database by the associated name.

A View is actually a combination of tables that exist in the form of a predefined PostgreSQL query.

View (views) can contain all rows of a table or select rows from one or more tables.

A View (view) can be created from one or more tables, depending on the PostgreSQL query in which the view is to be created.

A View (view) is a virtual table that allows users to implement the following:

The way in which users or user groups think it is more natural or intuitive to find structural data.
Restrict data access, and users can only see limited data, not complete tables.
Summarize the data from various tables to generate reports.

The PostgreSQL view is read-only and may not be executed on the view DELETE 、 INSERT Or UPDATE Statement. But you can create a trigger on the view when you try DELETE 、 INSERT Or UPDATE When the view is triggered, the actions to be done are defined in the contents of the trigger.

5.37.1. CREATE VIEW (create View) ¶

Used in PostgreSQL CREATE VIEW Statement to create views, which can be created from one table, multiple tables, or other views.

CREATE VIEW The basic syntax is as follows:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

You can find it in the SELECT Statement contains multiple tables, which is the same as in the normal SQL SELECT The manner in the query is very similar. If you use the optional TEMP Or TEMPORARY Keyword, the view is created in the tempdb.

5.37.2. Example ¶

Create COMPANY 表（下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now, here is an example from COMPANY Table creates an instance of the view. The view is only from COMPANY Select several columns from the table:

runoobdb=# CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

Now, you can query COMPANY_VIEW Similar to the way you query the actual table Here is an example:

runoobdb# SELECT * FROM COMPANY_VIEW;

The results are as follows:

id | name  | age
----+-------+-----
| Paul  |  32
| Allen |  25
| Teddy |  23
| Mark  |  25
| David |  27
| Kim   |  22
| James |  24
(7 rows)

5.37.3. DROP VIEW (Delete View) ¶

要删除视图，只需使用带有 view_name 的 DROP VIEW 语句。 DROP VIEW 的基本语法如下：

runoobdb=# DROP VIEW view_name;

The following command deletes the COMPANY_VIEW view we created earlier:

runoobdb=# DROP VIEW COMPANY_VIEW;