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.
