Creating a View

The following is the syntax for creating a view:

  CREATE VIEW 
view

           AS 
query

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.

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.

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.