<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title> Practical PostgreSQL</title>
<meta name="keywords" content="postgresql, support, recovery, design, software, hosting, expterise, programming, postgres, pgsql, replication, mysql, firebird, database, sql, free, open source, software" />
<meta name="description" content="Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. " />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="../css/layout.css" />
<link rel="stylesheet" type="text/css" href="../css/cmd4.css" />
<script language="javascript" type="text/javascript"><!--
greymenuhomewideLit = new Image();
greymenuhomewideLit.src = "/images/greymenu_homewide_lit.gif";
greymenuaboutLit = new Image();
greymenuaboutLit.src = "/images/greymenu_about_lit.gif";
greymenuproductsLit = new Image();
greymenuproductsLit.src = "/images/greymenu_products_lit.gif";
greymenuservicesLit = new Image();
greymenuservicesLit.src = "/images/greymenu_services_lit.gif";
greymenusupportLit = new Image();
greymenusupportLit.src = "/images/greymenu_support_lit.gif";
greymenucommunityLit = new Image();
greymenucommunityLit.src = "/images/greymenu_community_lit.gif";
//--></script>
</head>
<body>
<div align="center">
<div id="widecontainer">
<div id="widetop">
<div class="throwleft" style="width: 300px; text-align: left;"><a href="http://www.commandprompt.com/"><img src="../images/cmd4logo_wide.gif" border="0" alt="Command Prompt, Inc." /></a></div>
<div class="throwright" style="width: 400px; text-align: right;">
<div id="topright">
<form action="index.html" name="cmdform" method="post">
<ul>
<li><input type="text" name="search" value="" /></li>
<li><input type="submit" name="action" value="Search Book" /></li>
</ul>
</form>
</div>
</div>
</div>
<ul id="greymenu_wide">
<li><a href="http://www.commandprompt.com/home/"><img onmouseover="this.src = greymenuhomewideLit.src;" onmouseout="this.src = '/images/greymenu_homewide.gif';" src="../images/greymenu_homewide.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/about/"><img onmouseover="this.src = greymenuaboutLit.src;" onmouseout="this.src = '/images/greymenu_about.gif';" src="../images/greymenu_about.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/products/"><img onmouseover="this.src = greymenuproductsLit.src;" onmouseout="this.src = '/images/greymenu_products.gif';" src="../images/greymenu_products.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/services/"><img onmouseover="this.src = greymenuservicesLit.src;" onmouseout="this.src = '/images/greymenu_services.gif';" src="../images/greymenu_services.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/support/"><img onmouseover="this.src = greymenusupportLit.src;" onmouseout="this.src = '/images/greymenu_support.gif';" src="../images/greymenu_support.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/community/"><img onmouseover="this.src = greymenucommunityLit.src;" onmouseout="this.src = '/images/greymenu_community.gif';" src="../images/greymenu_community.gif" border="0" height="22" title="" alt="" /></a></li> </ul>
<div style="padding-left: 16px; width: 96%;">
<HTML
><HEAD
><TITLE
>Using Views</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.72
"><LINK
REL="HOME"
TITLE="Practical PostgreSQL"
href="book1"><LINK
REL="UP"
TITLE="Using SQL with PostgreSQL"
href="c4890"><LINK
REL="PREVIOUS"
TITLE="Using Sub-Queries"
href="x7300"><LINK
REL="NEXT"
TITLE="Further SQL Application"
href="x7543"></HEAD
><BODY
CLASS="SECT1"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>Practical PostgreSQL</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
href="x7300"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 4. Using SQL with PostgreSQL</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
href="x7543"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="AEN7379"
>Using Views</A
></H1
><P
>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 <SPAN
><I
CLASS="EMPHASIS"
>psql</I
></SPAN
>. Furthermore, it can be highly inefficient to pass excessively large
queries over a network to your PostgreSQL server for commonly executed routines.</P
><P
>This is where <SPAN
><I
CLASS="EMPHASIS"
>views</I
></SPAN
> can come in handy. Views can be thought of as stored queries, which allow you to
create a database object that functions very similarly to a table, but whose contents are dynamically and directly
reflective only of the rows which it is defined to select. Views are quite flexible in practice, in that they may address
common, simple queries to a single table, as well as extraordinarily complicated ones which may span across several
tables.</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN7390"
>Creating a View</A
></H2
><P
>The following is the syntax for creating a view:</P
><PRE
CLASS="SCREEN"
> CREATE VIEW <TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
>
AS <TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
></PRE
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
></DT
><DD
><P
> The name (identifier) of the view that you wish to create.
</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
></DT
><DD
><P
>The complete SQL <TT
CLASS="LITERAL"
>SELECT</TT
> query that defines the content of the view.</P
></DD
></DL
></DIV
>
</P
><P
>Imagine that you have a table called <TT
CLASS="LITERAL"
>shipments</TT
> 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 <A
href="x7379#THESHIPMENTSTABLEAPPLYING"
>Table 4-1</A
>.</P
><DIV
CLASS="TABLE"
><A
NAME="THESHIPMENTSTABLEAPPLYING"
></A
><P
><B
>Table 4-1. The shipments table</B
></P
><TABLE
BORDER="1"
WIDTH="100%"
CLASS="CALSTABLE"
><THEAD
><TR
><TH
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
>Column</P
></TH
><TH
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
>Type</P
></TH
><TH
WIDTH="20"
ALIGN="LEFT"
VALIGN="TOP"
><P
>Modifier</P
></TH
></TR
></THEAD
><TBODY
><TR
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>id</TT
></P
></TD
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>integer</TT
></P
></TD
><TD
WIDTH="20"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>NOT NULL DEFAULT nextval('shipments_ship_id_seq')</TT
></P
></TD
></TR
><TR
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>customer_id</TT
></P
></TD
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>integer</TT
></P
></TD
><TD
WIDTH="20"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
></TT
></P
></TD
></TR
><TR
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>isbn</TT
></P
></TD
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>text</TT
></P
></TD
><TD
WIDTH="20"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
></TT
></P
></TD
></TR
><TR
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>ship_date</TT
></P
></TD
><TD
WIDTH="4"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
>timestamp</TT
></P
></TD
><TD
WIDTH="20"
ALIGN="LEFT"
VALIGN="TOP"
><P
><TT
CLASS="LITERAL"
></TT
></P
></TD
></TR
></TBODY
></TABLE
></DIV
><P
>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:</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SELECT COUNT(*) FROM shipments;</B
></TT
>
count
-------
32
(1 row)</PRE
><P
>Remember that the asterisk (<TT
CLASS="LITERAL"
>*</TT
>) symbol in this query simply indicates to PostgreSQL
that all rows should be counted, regardless of <TT
CLASS="LITERAL"
>NULL</TT
> 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.</P
><P
>Increasing the complexity of this query, a <TT
CLASS="LITERAL"
>JOIN</TT
> clause can be attached to join the
<TT
CLASS="LITERAL"
>shipments</TT
> information with the <TT
CLASS="LITERAL"
>editions</TT
> and
<TT
CLASS="LITERAL"
>books</TT
> tables, in order to retrieve the title of each shipped book. Furthermore, a
<TT
CLASS="LITERAL"
>GROUP BY</TT
> clause can be added to the query in order to aggregate the shipments by their
titles.</P
><P
>Recall that by aggregating by the <TT
CLASS="LITERAL"
>title</TT
> column, the
<TT
CLASS="LITERAL"
>count()</TT
> function will count the number of rows per aggregated row (in this case, per
unique title). Finally, a <TT
CLASS="LITERAL"
>max()</TT
> function can be applied to the
<TT
CLASS="LITERAL"
>ship_date</TT
> column of the <TT
CLASS="LITERAL"
>shipments</TT
> table in order to see
the most recently shipped copy of each book, along with the counted number shipped:</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SELECT count(*) AS num_shipped, max(ship_date), title</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> FROM shipments</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> JOIN editions USING (isbn)</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> NATURAL JOIN books AS b (book_id)</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> GROUP BY b.title</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> ORDER BY num_shipped DESC;</B
></TT
>
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)</PRE
><P
>While obviously an informative query, the syntax can be somewhat too unwieldy to repeat frequently.
<A
href="x7379#CREATINGAVIEW"
>Example 4-62</A
> demonstrates creating a view on this same query with the <TT
CLASS="LITERAL"
>CREATE VIEW</TT
>
command.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="CREATINGAVIEW"
></A
><P
><B
>Example 4-62. Creating a view</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>CREATE VIEW recent_shipments</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> AS SELECT count(*) AS num_shipped, max(ship_date), title</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> FROM shipments</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> JOIN editions USING (isbn)</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> NATURAL JOIN books AS b (book_id)</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> GROUP BY b.title</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> ORDER BY num_shipped DESC;</B
></TT
>
CREATE</PRE
></DIV
><P
>The <TT
CLASS="LITERAL"
>CREATE</TT
> server response in <A
href="x7379#CREATINGAVIEW"
>Example 4-62</A
> confirms that the view
was accurately created. As a result, the Book Town database should now have a view called
<TT
CLASS="LITERAL"
>recent_shipments</TT
> 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.</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN7515"
>Applying Views</A
></H2
><P
>The key difference in the functionality of a view is that instead of having to type a long query, only a simple
<TT
CLASS="LITERAL"
>SELECT</TT
> command is needed, as shown in <A
href="x7379#USINGAVIEW"
>Example 4-63</A
>.</P
><DIV
CLASS="EXAMPLE"
><A
NAME="USINGAVIEW"
></A
><P
><B
>Example 4-63. Using a view</B
></P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SELECT * FROM recent_shipments;</B
></TT
>
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=# <TT
CLASS="USERINPUT"
><B
>SELECT * FROM recent_shipments</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> ORDER BY max DESC</B
></TT
>
booktown-# <TT
CLASS="USERINPUT"
><B
> LIMIT 3;</B
></TT
>
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)</PRE
></DIV
><P
><A
href="x7379#USINGAVIEW"
>Example 4-63</A
> further demonstrates that, even though the view was created with an
<TT
CLASS="LITERAL"
>ORDER BY</TT
> clause, the order of the view's result set itself can be re-sorted. This is
achieved by passing an <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause to the <TT
CLASS="LITERAL"
>SELECT</TT
>
command which is querying the view.</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>Any attempt to use <TT
CLASS="LITERAL"
>DELETE</TT
> or <TT
CLASS="LITERAL"
>UPDATE</TT
>
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.</P
></BLOCKQUOTE
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN7536"
>Destroying a view</A
></H2
><P
>The syntax to permanently destroy a view is entered as follows, where <TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
> is the name of the view
to be destroyed:</P
><PRE
CLASS="SCREEN"
> DROP VIEW <TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
></PRE
><P
>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.</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
href="x7300"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="book1"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
href="x7543"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Using Sub-Queries</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="c4890"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Further SQL Application</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
</div>
<br />
<div id="thefineprint">
<a href="http://www.commandprompt.com/products/mammothpostgresql/"><img src="../images/powered_by_mammoth.gif" alt="Powered by Mammoth PostgreSQL" title="Powered by Mammoth PostgreSQL" border="0" /></a>
<br />
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.
</div>
</div>
</div>
</body>
</html>