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.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:

PostgreSQL creates a temporary table by executing the SELECT command used to define the view.
PostgreSQL executes the SELECT command that you entered, substituting the name of temporary table everywhere that you used the name of the view.
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: