PostgreSQL, Second Edition

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