Perspectives are pseudo-tables. That is, they are not genuine tables; by and by show up as conventional tables to SELECT. A view can address a subset of a genuine table, choosing certain segments or certain columns from a common table. A view can even address joined tables. Since sees are appointed separate authorizations, you can utilize them to confine table access so the clients see just explicit lines or segments of a table.
A view can contain all lines of a table or chose lines from at least one tables. A view can be made from one or numerous tables, which relies upon the composed PostgreSQL inquiry to make a view.
Perspectives, which are somewhat virtual tables, permit clients to do the accompanying −
- Design information such that clients or classes of clients discover common or instinctive.
- Confine admittance to the information with the end goal that a client can just see restricted information rather than complete table.
- Sum up information from different tables, which can be utilized to produce reports.
Since sees are not normal tables, you will most likely be unable to execute a DELETE, INSERT, or UPDATE explanation on a view. Be that as it may, you can make a RULE to address this issue of utilizing DELETE, INSERT or UPDATE on a view.
Creating Views
The PostgreSQL sees are made utilizing the CREATE VIEW proclamation. The PostgreSQL perspectives can be made from a solitary table, numerous tables, or another view.
The fundamental CREATE VIEW punctuation is as per the following −
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
You can remember different tables for your SELECT assertion in very much like manner as you use them in ordinary PostgreSQL SELECT inquiry. On the off chance that the discretionary TEMP or TEMPORARY catchphrase is available, the view will be made in the brief space. Transitory perspectives are consequently dropped toward the finish of the current meeting.
Example
Consider, the COMPANY table is having the accompanying records −
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
Presently, following is a guide to make a view from COMPANY table. This view would be utilized to have just couple of sections from COMPANY table −
testdb=# CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM COMPANY;
Presently, you can inquiry COMPANY_VIEW likewise as you question a genuine table. Following is the model −
testdb=# SELECT * FROM COMPANY_VIEW;
This would create the accompanying outcome −
id | name | age
----+-------+-----
1 | Paul | 32
2 | Allen | 25
3 | Teddy | 23
4 | Mark | 25
5 | David | 27
6 | Kim | 22
7 | James | 24
(7 rows)
Dropping Views
To drop a view, essentially utilize the DROP VIEW explanation with the view_name. The essential DROP VIEW language structure is as per the following −
testdb=# DROP VIEW view_name;
The accompanying order will erase COMPANY_VIEW see, which we made in the last area −
testdb=# DROP VIEW COMPANY_VIEW;