<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_view.sgml,v 1.17 2002/04/23 02:07:16 tgl Exp $
PostgreSQL documentation
-->
<refentry id="SQL-CREATEVIEW">
<refmeta>
<refentrytitle id="SQL-CREATEVIEW-TITLE">CREATE VIEW</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>
CREATE VIEW
</refname>
<refpurpose>
define a new view
</refpurpose>
</refnamediv>
<refsynopsisdiv>
<refsynopsisdivinfo>
<date>2000-03-25</date>
</refsynopsisdivinfo>
<synopsis>
CREATE VIEW <replaceable class="PARAMETER">view</replaceable> [ ( <replaceable
class="PARAMETER">column name list</replaceable> ) ] AS SELECT <replaceable class="PARAMETER">query</replaceable>
</synopsis>
<refsect2 id="R2-SQL-CREATEVIEW-1">
<refsect2info>
<date>2000-03-25</date>
</refsect2info>
<title>
Inputs
</title>
<para>
<variablelist>
<varlistentry>
<term><replaceable class="parameter">view</replaceable></term>
<listitem>
<para>
The name (optionally schema-qualified) of a view to be created.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">column name list</replaceable></term>
<listitem>
<para>
An optional list of names to be used for columns of the view.
If given, these names override the column names that would be
deduced from the SQL query.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">query</replaceable></term>
<listitem>
<para>
An SQL query (that is, a <command>SELECT</> statement)
which will provide the columns and rows of the view.
</para>
<para>
Refer to <xref linkend="sql-select"> for more information
about valid arguments.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
<refsect2 id="R2-SQL-CREATEVIEW-2">
<refsect2info>
<date>2000-03-25</date>
</refsect2info>
<title>
Outputs
</title>
<para>
<variablelist>
<varlistentry>
<term><computeroutput>
CREATE
</computeroutput></term>
<listitem>
<para>
The message returned if the view is successfully created.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput>
ERROR: Relation '<replaceable class="parameter">view</replaceable>' already exists
</computeroutput></term>
<listitem>
<para>
This error occurs if the view specified already exists in the database.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><computeroutput>
WARNING: Attribute '<replaceable class="parameter">column</replaceable>' has an unknown type
</computeroutput></term>
<listitem>
<para>
The view will be created having a column with an unknown type
if you do not specify it. For example, the following command gives
a warning:
<programlisting>
CREATE VIEW vista AS SELECT 'Hello World'
</programlisting>
whereas this command does not:
<programlisting>
CREATE VIEW vista AS SELECT text 'Hello World'
</programlisting>
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
</refsynopsisdiv>
<refsect1 id="R1-SQL-CREATEVIEW-1">
<refsect1info>
<date>2000-03-25</date>
</refsect1info>
<title>
Description
</title>
<para>
<command>CREATE VIEW</command> will define a view of a query.
The view is not physically materialized. Instead, a query
rewrite rule (an <literal>ON SELECT</> rule) is automatically generated to
support SELECT operations on views.
</para>
<para>
If a schema name is given (for example, <literal>CREATE VIEW
myschema.myview ...</>) then the view is created in the
specified schema. Otherwise it is created in the current schema (the one
at the front of the search path; see <literal>CURRENT_SCHEMA()</>).
The view name must be distinct from the name of any other view, table,
sequence, or index in the same schema.
</para>
<refsect2 id="R2-SQL-CREATEVIEW-3">
<refsect2info>
<date>2000-03-25</date>
</refsect2info>
<title>
Notes
</title>
<para>
Currently, views are read only: the system will not allow an insert,
update, or delete on a view. You can get the effect of an updatable
view by creating rules that rewrite inserts, etc. on the view into
appropriate actions on other tables. For more information see
<xref linkend="sql-createrule">.
</para>
<para>
Use the <command>DROP VIEW</command> statement to drop views.
</para>
</refsect2>
</refsect1>
<refsect1 id="R1-SQL-CREATEVIEW-2">
<title>
Usage
</title>
<para>
Create a view consisting of all Comedy films:
<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
(2 rows)
</programlisting>
</para>
</refsect1>
<refsect1 id="R1-SQL-CREATEVIEW-3">
<title>
Compatibility
</title>
<refsect2 id="R2-SQL-CREATEVIEW-5">
<refsect2info>
<date>2000-03-25</date>
</refsect2info>
<title>
SQL92
</title>
<para>
SQL92 specifies some additional capabilities for the
<command>CREATE VIEW</command> statement:
</para>
<synopsis>
CREATE VIEW <replaceable class="parameter">view</replaceable> [ <replaceable class="parameter">column</replaceable> [, ...] ]
AS SELECT <replaceable class="parameter">expression</replaceable> [ AS <replaceable class="parameter">colname</replaceable> ] [, ...]
FROM <replaceable class="parameter">table</replaceable> [ WHERE <replaceable class="parameter">condition</replaceable> ]
[ WITH [ CASCADE | LOCAL ] CHECK OPTION ]
</synopsis>
<para>
The optional clauses for the full SQL92 command are:
<variablelist>
<varlistentry>
<term>CHECK OPTION</term>
<listitem>
<para>
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.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>LOCAL</term>
<listitem>
<para>
Check for integrity on this view.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>CASCADE</term>
<listitem>
<para>
Check for integrity on this view and on any dependent
view. CASCADE is assumed if neither CASCADE nor LOCAL is specified.
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</refsect2>
</refsect1>
</refentry>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->