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.
The following is the syntax for creating a view:
CREATE VIEW view AS query
viewThe name (identifier) of the view that you wish to create.
queryThe complete SQL
SELECTquery 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.
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.
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.
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 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.
