Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire