Using VIEW
In the previous section, I used the CREATE TABLE...AS command to create the info table so that you didn't have to type in the same complex query over and over again. The problem with that approach is that the info table is a snapshot of the underlying tables at the time that the CREATE TABLE...AS command was executed. If any of the underlying tables change (and they probably will), the info table will be out of synch.
Fortunately, PostgreSQL provides a much better solution to this problem—the view. A view is a named query. The syntax you use to create a view is nearly identical to the CREATE TABLE...AS command:
CREATE VIEW view AS select_clause;
Let's get rid of the info table and replace it with a view:
movies=# DROP TABLE info; DROP movies=# CREATE VIEW info AS movies-# SELECT customers.customer_name, rentals.tape_id,tapes.title movies-# FROM customers FULL OUTER JOIN rentals movies-# ON customers.customer_id = rentals.customer_id movies-# FULL OUTER JOIN tapes movies-# ON tapes.tape_id = rentals.tape_id; CREATE
While using psql, you can see a list of the views in your database using the dv meta-command:
movies=# dv
List of relations
Name | Type | Owner
------+------+---------------
info | view | bruce
(1 row)
You can see the definition of a view using the d view-name meta-command:
movies=# d info
View "info"
Attribute | Type | Modifier
---------------+-----------------------+----------
customer_name | character varying(50) |
tape_id | character(8) |
title | character varying(80) |
View definition: SELECT customers.customer_name,
rentals.tape_id, tapes.title
FROM (( customers FULL JOIN rentals
ON ((customers.customer_id = rentals.customer_id)))
FULL JOIN tapes
ON ((tapes.tape_id = rentals.tape_id)));
You can SELECT from a view in exactly the same way that you can SELECT from a table:
movies=# SELECT * FROM info WHERE tape_id IS NOT NULL; customer_name | tape_id | title ---------------+----------+--------------- Jones, Henry | AB-12345 | The Godfather Panky, Henry | AB-67472 | The Godfather Panky, Henry | MC-68873 | Casablanca Jones, Henry | OW-41221 | Citizen Kane (4 rows)
The great thing about a view is that it is always in synch with the underlying tables. Let's add a new rentals row:
movies=# INSERT INTO rentals VALUES( 'KJ-03335', '2001-11-26', 8 ); INSERT 38488 1
and then repeat the previous query:
movies=# SELECT * FROM info WHERE tape_id IS NOT NULL; customer_name | tape_id | title ---------------+----------+---------------------- Jones, Henry | AB-12345 | The Godfather Panky, Henry | AB-67472 | The Godfather Grumby, Jonas | KJ-03335 | American Citizen, An Panky, Henry | MC-68873 | Casablanca Jones, Henry | OW-41221 | Citizen Kane (5 rows)
To help you understand how a view works, you might imagine that the following sequence of events occurs each time you SELECT from a view:
1. | |
2. | PostgreSQL executes the SELECT command that you entered, substituting the name of temporary table everywhere that you used the name of the view. |
3. | PostgreSQL destroys the temporary table. |
This is not what actually occurs under the covers, but it's the easiest way to think about views.
Unlike other relational databases, PostgreSQL treats all views as read-only—you can't INSERT, DELETE, or UPDATE a view.
To destroy a view, you use the DROP VIEW command:
movies=# DROP VIEW info; DROP