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 problemthe 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.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.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. PostgreSQL creates a temporary table by executing the SELECT command used to define the view.

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