diff options
Diffstat (limited to 'doc/src/sgml/ref/create_view.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_view.sgml | 575 |
1 files changed, 575 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml new file mode 100644 index 0000000..3b26205 --- /dev/null +++ b/doc/src/sgml/ref/create_view.sgml @@ -0,0 +1,575 @@ +<!-- +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> + <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><literal>WITH ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )</literal></term> + <listitem> + <para> + This clause specifies optional parameters for a view; the following + parameters are supported: + + <variablelist> + <varlistentry> + <term><literal>check_option</literal> (<type>enum</type>)</term> + <listitem> + <para> + This parameter may be either <literal>local</literal> or + <literal>cascaded</literal>, and is equivalent to specifying + <literal>WITH [ CASCADED | LOCAL ] CHECK OPTION</literal> (see below). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>security_barrier</literal> (<type>boolean</type>)</term> + <listitem> + <para> + This should be used if the view is intended to provide row-level + security. See <xref linkend="rules-privileges"/> for full details. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>security_invoker</literal> (<type>boolean</type>)</term> + <listitem> + <para> + This option causes the underlying base relations to be checked + against the privileges of the user of the view rather than the view + owner. See the notes below for full details. + </para> + </listitem> + </varlistentry> + </variablelist> + + All of the above options can be changed on existing views using <link + linkend="sql-alterview"><command>ALTER VIEW</command></link>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">query</replaceable></term> + <listitem> + <para> + A <link linkend="sql-select"><command>SELECT</command></link> or + <link linkend="sql-values"><command>VALUES</command></link> command + which will provide the columns and rows of the view. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>WITH [ CASCADED | LOCAL ] CHECK OPTION</literal> + <indexterm zone="sql-createview"> + <primary>CHECK OPTION</primary> + </indexterm> + <indexterm zone="sql-createview"> + <primary>WITH CHECK OPTION</primary> + </indexterm> + </term> + <listitem> + <para> + This option controls the behavior of automatically updatable views. When + this option is specified, <command>INSERT</command> and <command>UPDATE</command> + commands on the view will be checked to ensure that new rows satisfy the + view-defining condition (that is, the new rows are checked to ensure that + they are visible through the view). If they are not, the update will be + rejected. If the <literal>CHECK OPTION</literal> is not specified, + <command>INSERT</command> and <command>UPDATE</command> commands on the view are + allowed to create rows that are not visible through the view. The + following check options are supported: + + <variablelist> + <varlistentry> + <term><literal>LOCAL</literal></term> + <listitem> + <para> + New rows are only checked against the conditions defined directly in + the view itself. Any conditions defined on underlying base views are + not checked (unless they also specify the <literal>CHECK OPTION</literal>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CASCADED</literal></term> + <listitem> + <para> + New rows are checked against the conditions of the view and all + underlying base views. If the <literal>CHECK OPTION</literal> is specified, + and neither <literal>LOCAL</literal> nor <literal>CASCADED</literal> is specified, + then <literal>CASCADED</literal> is assumed. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + + <para> + The <literal>CHECK OPTION</literal> may not be used with <literal>RECURSIVE</literal> + views. + </para> + + <para> + Note that the <literal>CHECK OPTION</literal> is only supported on views that + are automatically updatable, and do not have <literal>INSTEAD OF</literal> + triggers or <literal>INSTEAD</literal> rules. If an automatically updatable + view is defined on top of a base view that has <literal>INSTEAD OF</literal> + triggers, then the <literal>LOCAL CHECK OPTION</literal> may be used to check + the conditions on the automatically updatable view, but the conditions + on the base view with <literal>INSTEAD OF</literal> triggers will not be + checked (a cascaded check option will not cascade down to a + trigger-updatable view, and any check options defined directly on a + trigger-updatable view will be ignored). If the view or any of its base + relations has an <literal>INSTEAD</literal> rule that causes the + <command>INSERT</command> or <command>UPDATE</command> command to be rewritten, then + all check options will be ignored in the rewritten query, including any + checks from automatically updatable views defined on top of the relation + with the <literal>INSTEAD</literal> rule. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + Use the <link linkend="sql-dropview"><command>DROP VIEW</command></link> + 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 because the column name defaults to <literal>?column?</literal>; + also, the column data type defaults to <type>text</type>, which might not + be what you wanted. Better style for a string literal in a view's + result is something like: +<programlisting> +CREATE VIEW vista AS SELECT text 'Hello World' AS hello; +</programlisting> + </para> + + <para> + By default, access to the underlying base relations referenced in the view + is determined by the permissions of the view owner. In some cases, this + can be used to provide secure but restricted access to the underlying + tables. However, not all views are secure against tampering; see <xref + linkend="rules-privileges"/> for details. + </para> + + <para> + If the view has the <literal>security_invoker</literal> property set to + <literal>true</literal>, access to the underlying base relations is + determined by the permissions of the user executing the query, rather than + the view owner. Thus, the user of a security invoker view must have the + relevant permissions on the view and its underlying base relations. + </para> + + <para> + If any of the underlying base relations is a security invoker view, it + will be treated as if it had been accessed directly from the original + query. Thus, a security invoker view will always check its underlying + base relations using the permissions of the current user, even if it is + accessed from a view without the <literal>security_invoker</literal> + property. + </para> + + <para> + If any of the underlying base relations has + <link linkend="ddl-rowsecurity">row-level security</link> enabled, then + by default, the row-level security policies of the view owner are applied, + and access to any additional relations referred to by those policies is + determined by the permissions of the view owner. However, if the view has + <literal>security_invoker</literal> set to <literal>true</literal>, then + the policies and permissions of the invoking user are used instead, as if + the base relations had been referenced directly from the query using the + view. + </para> + + <para> + 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. + Functions in the view are executed with the privileges of the user + executing the query or the function owner, depending on whether the + functions are defined as <literal>SECURITY INVOKER</literal> or + <literal>SECURITY DEFINER</literal>. Thus, for example, calling + <literal>CURRENT_USER</literal> directly in a view will always return the + invoking user, not the view owner. This is not affected by the view's + <literal>security_invoker</literal> setting, and so a view with + <literal>security_invoker</literal> set to <literal>false</literal> is + <emphasis>not</emphasis> equivalent to a + <literal>SECURITY DEFINER</literal> function and those concepts should not + be confused. + </para> + + <para> + The user creating or replacing a view must have <literal>USAGE</literal> + privileges on any schemas referred to in the view query, in order to look + up the referenced objects in those schemas. Note, however, that this + lookup only happens when the view is created or replaced. Therefore, the + user of the view only requires the <literal>USAGE</literal> privilege on + the schema containing the view, not on the schemas referred to in the view + query, even for a security invoker view. + </para> + + <para> + When <command>CREATE OR REPLACE VIEW</command> is used on an existing + view, only the view's defining SELECT rule, plus any + <literal>WITH ( ... )</literal> parameters and its + <literal>CHECK OPTION</literal> are changed. + Other view properties, including ownership, permissions, and non-SELECT + rules, remain unchanged. You must own the view + to replace it (this includes being a member of the owning role). + </para> + + <refsect2 id="sql-createview-updatable-views"> + <title>Updatable Views</title> + + <indexterm zone="sql-createview-updatable-views"> + <primary>updatable views</primary> + </indexterm> + + <para> + Simple views are automatically updatable: the system will allow + <command>INSERT</command>, <command>UPDATE</command> and <command>DELETE</command> statements + to be used on the view in the same way as on a regular table. A view is + automatically updatable if it satisfies all of the following conditions: + + <itemizedlist> + <listitem> + <para> + The view must have exactly one entry in its <literal>FROM</literal> list, + which must be a table or another updatable view. + </para> + </listitem> + + <listitem> + <para> + The view definition must not contain <literal>WITH</literal>, + <literal>DISTINCT</literal>, <literal>GROUP BY</literal>, <literal>HAVING</literal>, + <literal>LIMIT</literal>, or <literal>OFFSET</literal> clauses at the top level. + </para> + </listitem> + + <listitem> + <para> + The view definition must not contain set operations (<literal>UNION</literal>, + <literal>INTERSECT</literal> or <literal>EXCEPT</literal>) at the top level. + </para> + </listitem> + + <listitem> + <para> + The view's select list must not contain any aggregates, window functions + or set-returning functions. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + An automatically updatable view may contain a mix of updatable and + non-updatable columns. A column is updatable if it is a simple reference + to an updatable column of the underlying base relation; otherwise the + column is read-only, and an error will be raised if an <command>INSERT</command> + or <command>UPDATE</command> statement attempts to assign a value to it. + </para> + + <para> + If the view is automatically updatable the system will convert any + <command>INSERT</command>, <command>UPDATE</command> or <command>DELETE</command> statement + on the view into the corresponding statement on the underlying base + relation. <command>INSERT</command> statements that have an <literal>ON + CONFLICT UPDATE</literal> clause are fully supported. + </para> + + <para> + If an automatically updatable view contains a <literal>WHERE</literal> + condition, the condition restricts which rows of the base relation are + available to be modified by <command>UPDATE</command> and <command>DELETE</command> + statements on the view. However, an <command>UPDATE</command> is allowed to + change a row so that it no longer satisfies the <literal>WHERE</literal> + condition, and thus is no longer visible through the view. Similarly, + an <command>INSERT</command> command can potentially insert base-relation rows + that do not satisfy the <literal>WHERE</literal> condition and thus are not + visible through the view (<literal>ON CONFLICT UPDATE</literal> may + similarly affect an existing row not visible through the view). + The <literal>CHECK OPTION</literal> may be used to prevent + <command>INSERT</command> and <command>UPDATE</command> commands from creating + such rows that are not visible through the view. + </para> + + <para> + If an automatically updatable view is marked with the + <literal>security_barrier</literal> property then all the view's <literal>WHERE</literal> + conditions (and any conditions using operators which are marked as <literal>LEAKPROOF</literal>) + will always be evaluated before any conditions that a user of the view has + added. See <xref linkend="rules-privileges"/> for full details. Note that, + due to this, rows which are not ultimately returned (because they do not + pass the user's <literal>WHERE</literal> conditions) may still end up being locked. + <command>EXPLAIN</command> can be used to see which conditions are + applied at the relation level (and therefore do not lock rows) and which are + not. + </para> + + <para> + A more complex view that does not satisfy all these conditions is + read-only by default: the system will not allow an insert, update, or + delete on the view. You can get the effect of an updatable view by + creating <literal>INSTEAD OF</literal> triggers on the view, which must + convert attempted inserts, etc. on the view into appropriate actions + on other tables. For more information see <xref + linkend="sql-createtrigger"/>. Another possibility is to create rules + (see <xref linkend="sql-createrule"/>), but in practice triggers are + easier to understand and use correctly. + </para> + + <para> + Note that the user performing the insert, update or delete on the view + must have the corresponding insert, update or delete privilege on the + view. In addition, by default, the view's owner must have the relevant + privileges on the underlying base relations, whereas the user performing + the update does not need any permissions on the underlying base relations + (see <xref linkend="rules-privileges"/>). However, if the view has + <literal>security_invoker</literal> set to <literal>true</literal>, the + user performing the update, rather than the view owner, must have the + relevant privileges on the underlying base relations. + </para> + </refsect2> + </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> + This will create a view containing the columns that are in the + <literal>film</literal> table at the time of view creation. Though + <literal>*</literal> was used to create the view, columns added later to + the table will not be part of the view. + </para> + + <para> + Create a view with <literal>LOCAL CHECK OPTION</literal>: + +<programlisting> +CREATE VIEW universal_comedies AS + SELECT * + FROM comedies + WHERE classification = 'U' + WITH LOCAL CHECK OPTION; +</programlisting> + This will create a view based on the <literal>comedies</literal> view, showing + only films with <literal>kind = 'Comedy'</literal> and + <literal>classification = 'U'</literal>. Any attempt to <command>INSERT</command> or + <command>UPDATE</command> a row in the view will be rejected if the new row + doesn't have <literal>classification = 'U'</literal>, but the film + <literal>kind</literal> will not be checked. + </para> + + <para> + Create a view with <literal>CASCADED CHECK OPTION</literal>: + +<programlisting> +CREATE VIEW pg_comedies AS + SELECT * + FROM comedies + WHERE classification = 'PG' + WITH CASCADED CHECK OPTION; +</programlisting> + This will create a view that checks both the <literal>kind</literal> and + <literal>classification</literal> of new rows. + </para> + + <para> + Create a view with a mix of updatable and non-updatable columns: + +<programlisting> +CREATE VIEW comedies AS + SELECT f.*, + country_code_to_name(f.country_code) AS country, + (SELECT avg(r.rating) + FROM user_ratings r + WHERE r.film_id = f.id) AS avg_rating + FROM films f + WHERE f.kind = 'Comedy'; +</programlisting> + This view will support <command>INSERT</command>, <command>UPDATE</command> and + <command>DELETE</command>. All the columns from the <literal>films</literal> table will + be updatable, whereas the computed columns <literal>country</literal> and + <literal>avg_rating</literal> will be read-only. + </para> + + <para> + Create a recursive view consisting of the numbers from 1 to 100: +<programlisting> +CREATE RECURSIVE VIEW public.nums_1_100 (n) AS + VALUES (1) +UNION ALL + SELECT n+1 FROM nums_1_100 WHERE n < 100; +</programlisting> + Notice that although the recursive view's name is schema-qualified in this + <command>CREATE</command>, its internal self-reference is not schema-qualified. + This is because the implicitly-created CTE's name cannot be + schema-qualified. + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>CREATE OR REPLACE VIEW</command> is a + <productname>PostgreSQL</productname> language extension. + So is the concept of a temporary view. + The <literal>WITH ( ... )</literal> clause is an extension as well, as are + security barrier views and security invoker views. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-alterview"/></member> + <member><xref linkend="sql-dropview"/></member> + <member><xref linkend="sql-creatematerializedview"/></member> + </simplelist> + </refsect1> +</refentry> |