Database Reference
In-Depth Information
Prior to the concept of
auto-updatable views
in PostgreSQL, one
could not perform an
INSERT
,
UPDATE
, or
DELETE
operation on
views and used rules or triggers to make this happen.
Databases are understood well with hands-on experience. We will irst write a code
for a simple view that will collect data from the
warehouse_tbl
table. Moving
forward, we will join two tables to see how it works.
As per the PostgreSQL manual, here is the general syntax to create a view:
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name
[ ( column_name [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
Now, proceed to actually create a view, which is as easy as creating a table, using the
following statement:
warehouse_db=# CREATE VIEW view_warehouse_tbl
AS SELECT *
FROM warehouse_tbl;
The preceding statement has created a view called
view_warehouse_tbl
in the
form of a
SELECT
query. You can access this view to see the results using the
following statement:
warehouse_db=# SELECT warehouse_id, warehouse_name, state FROM
view_warehouse_tbl;
warehouse_id | warehouse_name | state
--------------+----------------+-------
1 | Mark Corp | CT
2 | Bill & Co | CT
3 | West point | CT
5 | South point | LA
(4 rows)
Now, suppose your database design is required not to permit access to tables directly
but to give a restricted access to viewing the table data. For this, you should create a
view that joins multiple tables with selective columns.
