Practical PostgreSQL

Using Views

While working with SQL, times will often arise when you would like your statements to be re-usable. This is especially the case when working with large or intricate queries. There are few things more frustrating then having to re-type a long query over and over again within psql. Furthermore, it can be highly inefficient to pass excessively large queries over a network to your PostgreSQL server for commonly executed routines.

This is where views can come in handy. Views can be thought of as stored queries, which allow you to create a database object that functions very similarly to a table, but whose contents are dynamically and directly reflective only of the rows which it is defined to select. Views are quite flexible in practice, in that they may address common, simple queries to a single table, as well as extraordinarily complicated ones which may span across several tables.

Creating a View

The following is the syntax for creating a view:

CREATE VIEW view
         AS query

view

The name (identifier) of the view that you wish to create.

query

The complete SQL SELECT query that defines the content of the view.

Imagine that you have a table called shipments that relates a unique shipping identifier with a customer identifier, a book ISBN, and a timestamp reflecting when the book was shipped. This table is shown in Table 4-1.

Table 4-1. The shipments table

Column	Type	Modifier
id	integer	NOT NULL DEFAULT nextval('shipments_ship_id_seq')
customer_id	integer
isbn	text
ship_date ...