PostgreSQL - SPLessons

PostgreSQL Views

Chapter 21

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PostgreSQL Views

PostgreSQL Views

shape Description

PostgreSQL Views is a database object that can be created on a table to improve the performance of PostgreSQL Server.A PostgreSQL Views has unique look on data from one or more tables. It can organize data in unique order, focus or hide some data. A view comprises of a stored query accessible as a fundamental table composed of the outcome set. Beside standard tables a perspective does not shaped a part of the physical schema. It is a virtual table, dynamic in the database.View is a stored query, and it can be attributed like a table.

PostgreSQL Views object mainly classified into

Create view

shape Description

Once a PostgreSQL Views is created from base table, all DML operations an be performed on that view which effects its base table. This kind of view is called simple view.

Create any number of columns in a table. The system privileges are required to create own schema, and can execute any object privilege on object type.


shape Syntax


create view <table_name>as select <column_name> FROM <table_name> where <condition>;

Table_name => Any accurate table.

column_name=>The columns that inserted in the table.

Condition => The condition to get the specific result set.

shape Examples

By viewing the below example, the concepts of PostgreSQL view can be understand easily.

SQLDB=# select * from cars;
car_id | car_name | cost   
-------+----------+--------
    1  | audi     |  52642 
    2  | skoda    | 526400 
    3  | volva    |  52640 
    4  | volva    |  52000 
    5  | hummer   |  41400 
(5 rows) 

SQLDB=# CREATE VIEW Cheapcars AS SELECT car_name from cars where "cost"<52640; 
Query returns successfully
SQLDB=# select * from cheapcars;
car_name 
---------
 volva    
 hummer   
(2 rows)

In the above example,a simple table cars has been created and by applying view operation to that table,like inserting some condition on column name.(i.e.,cost of car<52640 is the condition,and it show the cost of cars less than that value).

Alter view

shape Description

Once a view is created from base table,all alter operations can be performed on that view which effects its base table.The alter view proclamation is like the create view proclamation aside from create is replaced with alter keyword.

shape Syntax


Alter view <table_name> as select <column_name> from <table_name> where condition;
Table_name => Any accurate table.

column_name=>The columns that inserted in the table.

Condition => The condition to get the specific result set.

shape Examples

SQLDB=# select * from cars;
car_id | car_name | cost   
-------+----------+--------
    1  | audi     |  52642 
    2  | skoda    | 526400 
    3  | volva    |  52640 
    4  | volva    |  52000 
    5  | hummer   |  41400 
(5 rows)

SQLDB=# alter view cheapcars as select car_name from cars where cost<52640;
Query returns successfully

SQLDB=# select * from cheapcars;
car_name 
---------
volva    
hummer   
(2 rows)

In the above example, a simple table cars has been created and then alter the table by applying view operations to that table, like adding some condition on column values using where clause.(The column name cost have been alter by using where clause, i.e,cost<52640 will be show in the result set.)

Drop view

shape Description

Drop table will drop the table.In the same way drop view will drop the permission of view table,and there is no such table exist in the data bases.

shape Syntax

Drop table <table_name>

Table_name=>The accurate table stored in the database.

shape Examples

SQLDB=# drop table cars;
Query Returned Successfully
SQLDB=# select * from cheapcars;
ERROR 1356 (HY000): View 'employee.cheapcars' references invalid table(s) or col
umn(s) or function(s) or definer/invoker of view lack rights to use them

In the above example,the table name cars have been dropped from the database,and cannot perform any operations on cars related to cheapcars.

Summary

shape Key Points

  • View -View is a database object.
  • Create view – DML operations can be performed in create view.
  • Alter view – DML operation can be performed in alter view.
  • Drop view – drop view will drop a table.