File: sql-createview.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (416 lines) | stat: -rw-r--r-- 5,762 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>   CREATE VIEW
  </TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="   CREATE USER
  "
HREF="sql-createuser.html"><LINK
REL="NEXT"
TITLE="   DECLARE
  "
HREF="sql-declare.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-createuser.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-declare.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>CREATE VIEW</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>   CREATE VIEW
   &#8212; Constructs a virtual table
  </DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>CREATE VIEW <TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
>
    AS SELECT <TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
>
  </PRE
><DIV
CLASS="REFSECT2"
><H3
>    Inputs
   </H3
><P
>       <P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
></SPAN
></DT
><DD
><P
>          The name of a view to be created.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
><TT
CLASS="REPLACEABLE"
><I
>query</I
></TT
></SPAN
></DT
><DD
><P
>An SQL query which will provide the columns and rows of the view.
	  </P
><P
>	   Refer to the SELECT statement for more information
	   about valid arguments.
	  </P
></DD
></DL
><P>
  </P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>    Outputs
   </H3
><P
>       <P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>CREATE</SPAN
></DT
><DD
><P
>          The message returned if the view is successfully created.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>	   WARN amcreate: "<TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
>" relation already exists
	  </SPAN
></DT
><DD
><P
>          This error occurs if the view specified already exists in the database.
	  </P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>NOTICE create: attribute named "<TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
>" has an unknown type</SPAN
></DT
><DD
><P
>          The view will be created having a column with an unknown type
	   if you do not specify it. For example, the following command gives
	   an error:
	   <PRE
CLASS="PROGRAMLISTING"
>	    CREATE VIEW vista AS SELECT 'Hello World'
	   </PRE
>
	   whereas this command does not:
	   <PRE
CLASS="PROGRAMLISTING"
>	    CREATE VIEW vista AS SELECT 'Hello World'::text
	   </PRE
>

   </P
></DD
></DL
><P>
  </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Description
  </H2
><P
>   CREATE VIEW will define a view of a table. This view is
   not physically materialized. Specifically, a query
   rewrite retrieve rule is automatically generated
   to support retrieve operations on views.
  </P
><DIV
CLASS="REFSECT2"
><H3
>    Notes
   </H3
><P
>   Use the DROP VIEW statement to drop views.
   </P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>    Bugs
   </H3
><P
>   Currently, views are read only.
   </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Usage
  </H2
><P
>   Create a view consisting of all Comedy films:
  </P
><PRE
CLASS="PROGRAMLISTING"
>CREATE VIEW kinds AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

SELECT * FROM kinds;

code |title                    |did| date_prod|kind      |len
-----+-------------------------+---+----------+----------+------
UA502|Bananas                  |105|1971-07-13|Comedy    | 01:22
C_701|There's a Girl in my Soup|107|1970-06-11|Comedy    | 01:36
  </PRE
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Compatibility
  </H2
><P
>  </P
><DIV
CLASS="REFSECT2"
><H3
>    SQL92
   </H3
><P
>   SQL92 specifies some additional capabilities for the CREATE VIEW statement:
   </P
><PRE
CLASS="SYNOPSIS"
>CREATE VIEW <TT
CLASS="REPLACEABLE"
><I
>view</I
></TT
> [ <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ...] ]
    AS SELECT <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> [AS <TT
CLASS="REPLACEABLE"
><I
>colname</I
></TT
>] [, ...]
    FROM <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>
    [ WHERE <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
> ]
    [ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
   </PRE
><P
></P
><DL
><DT
></DT
><DD
><P
>       <P
></P
></P><DL
><DT
>CHECK OPTION</DT
><DD
><P
> This option is to do with updatable views.
                     All INSERTs and UPDATEs on the view will be
                     checked to ensure data satisfy the view-defining
                     condition. If they do not, the update will be rejected.
	  </P
></DD
><DT
>LOCAL</DT
><DD
><P
>	   Check for integrity on this view.
	  </P
></DD
><DT
>CASCADE</DT
><DD
><P
>	   Check for integrity on this view and on any dependent
	   view. CASCADE is assumed if neither CASCADE nor LOCAL is specified.
	  </P
></DD
></DL
><P>
      </P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-createuser.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-declare.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>CREATE USER</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>DECLARE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>