| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.33 2006/09/18 19:54:01 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> |
| |
| <indexterm zone="sql-createview"> |
| <primary>CREATE VIEW</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] |
| AS <replaceable class="PARAMETER">query</replaceable> |
| </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. You can only replace |
| a view with a new query that generates the identical set of columns |
| (i.e., same column names and data types). |
| </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. Temporary |
| views exist in a special schema, so a schema name may not be given |
| when creating a temporary view. The name of the view must be |
| distinct from the name of any other view, table, sequence, or index |
| in the same schema. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>TEMPORARY</> or <literal>TEMP</></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><replaceable class="parameter">name</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</replaceable></term> |
| <listitem> |
| <para> |
| An optional list of names to be used for columns of the view. |
| If not given, the column names are deduced from the query. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">query</replaceable></term> |
| <listitem> |
| <para> |
| A <xref linkend="sql-select" endterm="sql-select-title"> or |
| <xref linkend="sql-values" endterm="sql-values-title"> command |
| which will provide the columns and rows of the view. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <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" endterm="sql-createrule-title">. |
| </para> |
| |
| <para> |
| Use the <xref linkend="sql-dropview" endterm="sql-dropview-title"> |
| statement to drop views. |
| </para> |
| |
| <para> |
| Be careful that the names and types of the view's columns will be |
| assigned the way you want. For example, |
| <programlisting> |
| CREATE VIEW vista AS SELECT 'Hello World'; |
| </programlisting> |
| is bad form in two ways: the column name defaults to <literal>?column?</>, |
| and the column data type defaults to <type>unknown</>. If you want a |
| string literal in a view's result, use something like |
| <programlisting> |
| CREATE VIEW vista AS SELECT text 'Hello World' AS hello; |
| </programlisting> |
| </para> |
| |
| <para> |
| Access to tables referenced in the view is determined by permissions of |
| the view owner. However, functions called in the view are treated the |
| same as if they had been called directly from the query using the view. |
| Therefore the user of a view must have permissions to call all functions |
| used by the view. |
| </para> |
| |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| Create a view consisting of all comedy films: |
| |
| <programlisting> |
| CREATE VIEW comedies AS |
| SELECT * |
| FROM films |
| WHERE kind = 'Comedy'; |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| The SQL standard specifies some additional capabilities for the |
| <command>CREATE VIEW</command> statement: |
| <synopsis> |
| CREATE VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] |
| AS <replaceable class="PARAMETER">query</replaceable> |
| [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] |
| </synopsis> |
| </para> |
| |
| <para> |
| The optional clauses for the full SQL command are: |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>CHECK OPTION</literal></term> |
| <listitem> |
| <para> |
| This option has to do with updatable views. All |
| <command>INSERT</> and <command>UPDATE</> commands on the view |
| will be checked to ensure data satisfy the view-defining |
| condition (that is, the new data would be visible through the |
| view). If they do not, the update will be rejected. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>LOCAL</literal></term> |
| <listitem> |
| <para> |
| Check for integrity on this view. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>CASCADED</literal></term> |
| <listitem> |
| <para> |
| Check for integrity on this view and on any dependent |
| view. <literal>CASCADED</> is assumed if neither |
| <literal>CASCADED</> nor <literal>LOCAL</> is specified. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </para> |
| |
| <para> |
| <command>CREATE OR REPLACE VIEW</command> is a |
| <productname>PostgreSQL</productname> language extension. |
| So is the concept of a temporary view. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-dropview" endterm="sql-dropview-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |