<!--
doc/src/sgml/ref/create_view.sgml
PostgreSQL documentation
-->

<refentry id="sql-createview">
 <indexterm zone="sql-createview">
  <primary>CREATE VIEW</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE VIEW</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE VIEW</refname>
  <refpurpose>define a new view</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
    [ WITH ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] ) ]
    AS <replaceable class="parameter">query</replaceable>
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE VIEW</command> defines a view of a query.  The view
   is not physically materialized. Instead, the query is run every time
   the view is referenced in a query.
  </para>

  <para>
   <command>CREATE OR REPLACE VIEW</command> is similar, but if a view
   of the same name already exists, it is replaced.  The new query must
   generate the same columns that were generated by the existing view query
   (that is, the same column names in the same order and with the same data
   types), but it may add additional columns to the end of the list.  The
   calculations giving rise to the output columns may be completely different.
  </para>

  <para>
   If a schema name is given (for example, <literal>CREATE VIEW
   myschema.myview ...</literal>) then the view is created in the specified
   schema.  Otherwise it is created in the current schema.  Temporary
   views exist in a special schema, so a schema name cannot be given
   when creating a temporary view. The name of the view must be
   distinct from the name of any other relation (table, sequence, index, view,
   materialized view, or foreign table) in the same schema.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
    <listitem>
     <para>
      If specified, the view is created as a temporary view.
      Temporary views are automatically dropped at the end of the
      current session.  Existing
      permanent relations with the same name are not visible to the
      current session while the temporary view exists, unless they are
      referenced with schema-qualified names.
     </para>

     <para>
      If any of the tables referenced by the view are temporary,
      the view is created as a temporary view (whether
      <literal>TEMPORARY</literal> is specified or not).
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>RECURSIVE</literal>
      <indexterm zone="sql-createview">
       <primary>RECURSIVE</primary>
       <secondary>in views</secondary>
      </indexterm>
    </term>
    <listitem>
     <para>
      Creates a recursive view.  The syntax
<synopsis>
CREATE RECURSIVE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</replaceable> (<replaceable>column_names</replaceable>) AS SELECT <replaceable>...</replaceable>;
</synopsis>
      is equivalent to
<synopsis>
CREATE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</replaceable> AS WITH RECURSIVE <replaceable>view_name</replaceable> (<replaceable>column_names</replaceable>) AS (SELECT <replaceable>...</replaceable>) SELECT <replaceable>column_names</replaceable> FROM <replaceable>view_name</replaceable>;
</synopsis>
      A view column name list must be specified for a recursive view.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">name</replaceable></term>
    <listitem>