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

Creating a View

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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