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	timestamp	Â

Now, imagine that you are interested in seeing how many shipments have been made and logged into this table. There are several ways that you can achieve the results you are looking for, but to keep things simple, you can begin with a query like this:

booktown=# SELECT COUNT(*) FROM shipments;
 count
-------
    32
(1 row)

Remember that the asterisk (*) symbol in this query simply indicates to PostgreSQL that all rows should be counted, regardless of NULL values that may exist in an otherwise specified column name. The query counts the number of total rows that return from the query, and thus the number of logged shipments.

Increasing the complexity of this query, a JOIN clause can be attached to join the shipments information with the editions and books tables, in order to retrieve the title of each shipped book. Furthermore, a GROUP BY clause can be added to the query in order to aggregate the shipments by their titles.

Recall that by aggregating by the title column, the count() function will count the number of rows per aggregated row (in this case, per unique title). Finally, a max() function can be applied to the ship_date column of the shipments table in order to see the most recently shipped copy of each book, along with the counted number shipped:

booktown=# SELECT count(*) AS num_shipped, max(ship_date), title
booktown-#        FROM shipments
booktown-#        JOIN editions USING (isbn)
booktown-#        NATURAL JOIN books AS b (book_id)
booktown-#        GROUP BY b.title
booktown-#        ORDER BY num_shipped DESC;
 num_shipped |          max           |            title
-------------+------------------------+-----------------------------
           5 | 2001-08-13 09:47:04-07 | The Cat in the Hat
           5 | 2001-08-14 13:45:51-07 | The Shining
           4 | 2001-08-11 09:55:05-07 | Bartholomew and the Oobleck
           3 | 2001-08-14 13:49:00-07 | Franklin in the Dark
           3 | 2001-08-15 11:57:40-07 | Goodnight Moon
           3 | 2001-08-14 13:41:39-07 | The Tell-Tale Heart
           2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey
           2 | 2001-08-14 08:42:58-07 | Dune
           2 | 2001-08-07 13:00:48-07 | Little Women
           2 | 2001-08-09 09:30:46-07 | The Velveteen Rabbit
           1 | 2001-08-14 07:33:47-07 | Dynamic Anatomy
(11 rows)

While obviously an informative query, the syntax can be somewhat too unwieldy to repeat frequently. Example 4-62 demonstrates creating a view on this same query with the CREATE VIEW command.

Example 4-62. Creating a view

booktown=# CREATE VIEW recent_shipments
booktown-#        AS SELECT count(*) AS num_shipped, max(ship_date), title
booktown-#           FROM shipments
booktown-#           JOIN editions USING (isbn)
booktown-#           NATURAL JOIN books AS b (book_id)
booktown-#           GROUP BY b.title
booktown-#           ORDER BY num_shipped DESC;
CREATE

The CREATE server response in Example 4-62 confirms that the view was accurately created. As a result, the Book Town database should now have a view called recent_shipments that will show each title that has been shipped from Book Town, how many of each title was shipped, and when the most recent shipment of that title occurred.

Applying Views

The key difference in the functionality of a view is that instead of having to type a long query, only a simple SELECT command is needed, as shown in Example 4-63.

Example 4-63. Using a view

booktown=# SELECT * FROM recent_shipments;
 num_shipped |          max           |            title
-------------+------------------------+-----------------------------
           5 | 2001-08-13 09:47:04-07 | The Cat in the Hat
           5 | 2001-08-14 13:45:51-07 | The Shining
           4 | 2001-08-11 09:55:05-07 | Bartholomew and the Oobleck
           3 | 2001-08-14 13:49:00-07 | Franklin in the Dark
           3 | 2001-08-15 11:57:40-07 | Goodnight Moon
           3 | 2001-08-14 13:41:39-07 | The Tell-Tale Heart
           2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey
           2 | 2001-08-14 08:42:58-07 | Dune
           2 | 2001-08-07 13:00:48-07 | Little Women
           2 | 2001-08-09 09:30:46-07 | The Velveteen Rabbit
           1 | 2001-08-14 07:33:47-07 | Dynamic Anatomy
(11 rows)

booktown=# SELECT * FROM recent_shipments
booktown-#        ORDER BY max DESC
booktown-#        LIMIT 3;
 num_shipped |          max           |         title
-------------+------------------------+-----------------------
           2 | 2001-08-15 14:02:01-07 | 2001: A Space Odyssey
           3 | 2001-08-15 11:57:40-07 | Goodnight Moon
           3 | 2001-08-14 13:49:00-07 | Franklin in the Dark
(3 rows)

Example 4-63 further demonstrates that, even though the view was created with an ORDER BY clause, the order of the view’s result set itself can be re-sorted. This is achieved by passing an ORDER BY clause to the SELECT command which is querying the view.

Note

Any attempt to use DELETE or UPDATE on a view will result in an error, as a view itself does not contain data. The view is merely a window to another set of data, despite its similar functional appearance to a table, and is not itself a modifiable data set.

Destroying a view

The syntax to permanently destroy a view is entered as follows, where view is the name of the view to be destroyed:

DROP VIEW view

The destruction of a view will have no effect on the data that the view utilizes. A view exists purely as a means to observe data in other tables, and may be safely destroyed without losing data (though the query described by the view will, of course, be lost). Thus any attempts to alter or delete from a view will fail.