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