diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createview.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createview.html | 346 |
1 files changed, 346 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createview.html b/doc/src/sgml/html/sql-createview.html new file mode 100644 index 0000000..ea1819e --- /dev/null +++ b/doc/src/sgml/html/sql-createview.html @@ -0,0 +1,346 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE VIEW</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createusermapping.html" title="CREATE USER MAPPING" /><link rel="next" href="sql-deallocate.html" title="DEALLOCATE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE VIEW</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createusermapping.html" title="CREATE USER MAPPING">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-deallocate.html" title="DEALLOCATE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEVIEW"><div class="titlepage"></div><a id="id-1.9.3.97.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE VIEW</span></h2><p>CREATE VIEW — define a new view</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW <em class="replaceable"><code>name</code></em> [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ] + [ WITH ( <em class="replaceable"><code>view_option_name</code></em> [= <em class="replaceable"><code>view_option_value</code></em>] [, ... ] ) ] + AS <em class="replaceable"><code>query</code></em> + [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] +</pre></div><div class="refsect1" id="id-1.9.3.97.5"><h2>Description</h2><p> + <code class="command">CREATE VIEW</code> 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. + </p><p> + <code class="command">CREATE OR REPLACE VIEW</code> 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. + </p><p> + If a schema name is given (for example, <code class="literal">CREATE VIEW + myschema.myview ...</code>) 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. + </p></div><div class="refsect1" id="id-1.9.3.97.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code></span></dt><dd><p> + 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. + </p><p> + If any of the tables referenced by the view are temporary, + the view is created as a temporary view (whether + <code class="literal">TEMPORARY</code> is specified or not). + </p></dd><dt><span class="term"><code class="literal">RECURSIVE</code> + <a id="id-1.9.3.97.6.2.2.1.2" class="indexterm"></a> + </span></dt><dd><p> + Creates a recursive view. The syntax +</p><pre class="synopsis"> +CREATE RECURSIVE VIEW [ <em class="replaceable"><code>schema</code></em> . ] <em class="replaceable"><code>view_name</code></em> (<em class="replaceable"><code>column_names</code></em>) AS SELECT <em class="replaceable"><code>...</code></em>; +</pre><p> + is equivalent to +</p><pre class="synopsis"> +CREATE VIEW [ <em class="replaceable"><code>schema</code></em> . ] <em class="replaceable"><code>view_name</code></em> AS WITH RECURSIVE <em class="replaceable"><code>view_name</code></em> (<em class="replaceable"><code>column_names</code></em>) AS (SELECT <em class="replaceable"><code>...</code></em>) SELECT <em class="replaceable"><code>column_names</code></em> FROM <em class="replaceable"><code>view_name</code></em>; +</pre><p> + A view column name list must be specified for a recursive view. + </p></dd><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of a view to be created. + </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p> + An optional list of names to be used for columns of the view. + If not given, the column names are deduced from the query. + </p></dd><dt><span class="term"><code class="literal">WITH ( <em class="replaceable"><code>view_option_name</code></em> [= <em class="replaceable"><code>view_option_value</code></em>] [, ... ] )</code></span></dt><dd><p> + This clause specifies optional parameters for a view; the following + parameters are supported: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">check_option</code> (<code class="type">enum</code>)</span></dt><dd><p> + This parameter may be either <code class="literal">local</code> or + <code class="literal">cascaded</code>, and is equivalent to specifying + <code class="literal">WITH [ CASCADED | LOCAL ] CHECK OPTION</code> (see below). + </p></dd><dt><span class="term"><code class="literal">security_barrier</code> (<code class="type">boolean</code>)</span></dt><dd><p> + This should be used if the view is intended to provide row-level + security. See <a class="xref" href="rules-privileges.html" title="41.5. Rules and Privileges">Section 41.5</a> for full details. + </p></dd><dt><span class="term"><code class="literal">security_invoker</code> (<code class="type">boolean</code>)</span></dt><dd><p> + 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. + </p></dd></dl></div><p> + + All of the above options can be changed on existing views using <a class="link" href="sql-alterview.html" title="ALTER VIEW"><code class="command">ALTER VIEW</code></a>. + </p></dd><dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt><dd><p> + A <a class="link" href="sql-select.html" title="SELECT"><code class="command">SELECT</code></a> or + <a class="link" href="sql-values.html" title="VALUES"><code class="command">VALUES</code></a> command + which will provide the columns and rows of the view. + </p></dd><dt><span class="term"><code class="literal">WITH [ CASCADED | LOCAL ] CHECK OPTION</code> + <a id="id-1.9.3.97.6.2.7.1.2" class="indexterm"></a> + <a id="id-1.9.3.97.6.2.7.1.3" class="indexterm"></a> + </span></dt><dd><p> + This option controls the behavior of automatically updatable views. When + this option is specified, <code class="command">INSERT</code> and <code class="command">UPDATE</code> + 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 <code class="literal">CHECK OPTION</code> is not specified, + <code class="command">INSERT</code> and <code class="command">UPDATE</code> commands on the view are + allowed to create rows that are not visible through the view. The + following check options are supported: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">LOCAL</code></span></dt><dd><p> + 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 <code class="literal">CHECK OPTION</code>). + </p></dd><dt><span class="term"><code class="literal">CASCADED</code></span></dt><dd><p> + New rows are checked against the conditions of the view and all + underlying base views. If the <code class="literal">CHECK OPTION</code> is specified, + and neither <code class="literal">LOCAL</code> nor <code class="literal">CASCADED</code> is specified, + then <code class="literal">CASCADED</code> is assumed. + </p></dd></dl></div><p> + </p><p> + The <code class="literal">CHECK OPTION</code> may not be used with <code class="literal">RECURSIVE</code> + views. + </p><p> + Note that the <code class="literal">CHECK OPTION</code> is only supported on views that + are automatically updatable, and do not have <code class="literal">INSTEAD OF</code> + triggers or <code class="literal">INSTEAD</code> rules. If an automatically updatable + view is defined on top of a base view that has <code class="literal">INSTEAD OF</code> + triggers, then the <code class="literal">LOCAL CHECK OPTION</code> may be used to check + the conditions on the automatically updatable view, but the conditions + on the base view with <code class="literal">INSTEAD OF</code> 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 <code class="literal">INSTEAD</code> rule that causes the + <code class="command">INSERT</code> or <code class="command">UPDATE</code> 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 <code class="literal">INSTEAD</code> rule. + </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.97.7"><h2>Notes</h2><p> + Use the <a class="link" href="sql-dropview.html" title="DROP VIEW"><code class="command">DROP VIEW</code></a> + statement to drop views. + </p><p> + Be careful that the names and types of the view's columns will be + assigned the way you want. For example: +</p><pre class="programlisting"> +CREATE VIEW vista AS SELECT 'Hello World'; +</pre><p> + is bad form because the column name defaults to <code class="literal">?column?</code>; + also, the column data type defaults to <code class="type">text</code>, which might not + be what you wanted. Better style for a string literal in a view's + result is something like: +</p><pre class="programlisting"> +CREATE VIEW vista AS SELECT text 'Hello World' AS hello; +</pre><p> + </p><p> + 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 <a class="xref" href="rules-privileges.html" title="41.5. Rules and Privileges">Section 41.5</a> for details. + </p><p> + If the view has the <code class="literal">security_invoker</code> property set to + <code class="literal">true</code>, 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. + </p><p> + 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 <code class="literal">security_invoker</code> + property. + </p><p> + If any of the underlying base relations has + <a class="link" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">row-level security</a> 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 + <code class="literal">security_invoker</code> set to <code class="literal">true</code>, 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. + </p><p> + 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 <code class="literal">SECURITY INVOKER</code> or + <code class="literal">SECURITY DEFINER</code>. Thus, for example, calling + <code class="literal">CURRENT_USER</code> directly in a view will always return the + invoking user, not the view owner. This is not affected by the view's + <code class="literal">security_invoker</code> setting, and so a view with + <code class="literal">security_invoker</code> set to <code class="literal">false</code> is + <span class="emphasis"><em>not</em></span> equivalent to a + <code class="literal">SECURITY DEFINER</code> function and those concepts should not + be confused. + </p><p> + The user creating or replacing a view must have <code class="literal">USAGE</code> + 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 <code class="literal">USAGE</code> privilege on + the schema containing the view, not on the schemas referred to in the view + query, even for a security invoker view. + </p><p> + When <code class="command">CREATE OR REPLACE VIEW</code> is used on an existing + view, only the view's defining SELECT rule, plus any + <code class="literal">WITH ( ... )</code> parameters and its + <code class="literal">CHECK OPTION</code> 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). + </p><div class="refsect2" id="SQL-CREATEVIEW-UPDATABLE-VIEWS"><h3>Updatable Views</h3><a id="id-1.9.3.97.7.11.2" class="indexterm"></a><p> + Simple views are automatically updatable: the system will allow + <code class="command">INSERT</code>, <code class="command">UPDATE</code> and <code class="command">DELETE</code> 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: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + The view must have exactly one entry in its <code class="literal">FROM</code> list, + which must be a table or another updatable view. + </p></li><li class="listitem"><p> + The view definition must not contain <code class="literal">WITH</code>, + <code class="literal">DISTINCT</code>, <code class="literal">GROUP BY</code>, <code class="literal">HAVING</code>, + <code class="literal">LIMIT</code>, or <code class="literal">OFFSET</code> clauses at the top level. + </p></li><li class="listitem"><p> + The view definition must not contain set operations (<code class="literal">UNION</code>, + <code class="literal">INTERSECT</code> or <code class="literal">EXCEPT</code>) at the top level. + </p></li><li class="listitem"><p> + The view's select list must not contain any aggregates, window functions + or set-returning functions. + </p></li></ul></div><p> + </p><p> + 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 <code class="command">INSERT</code> + or <code class="command">UPDATE</code> statement attempts to assign a value to it. + </p><p> + If the view is automatically updatable the system will convert any + <code class="command">INSERT</code>, <code class="command">UPDATE</code> or <code class="command">DELETE</code> statement + on the view into the corresponding statement on the underlying base + relation. <code class="command">INSERT</code> statements that have an <code class="literal">ON + CONFLICT UPDATE</code> clause are fully supported. + </p><p> + If an automatically updatable view contains a <code class="literal">WHERE</code> + condition, the condition restricts which rows of the base relation are + available to be modified by <code class="command">UPDATE</code> and <code class="command">DELETE</code> + statements on the view. However, an <code class="command">UPDATE</code> is allowed to + change a row so that it no longer satisfies the <code class="literal">WHERE</code> + condition, and thus is no longer visible through the view. Similarly, + an <code class="command">INSERT</code> command can potentially insert base-relation rows + that do not satisfy the <code class="literal">WHERE</code> condition and thus are not + visible through the view (<code class="literal">ON CONFLICT UPDATE</code> may + similarly affect an existing row not visible through the view). + The <code class="literal">CHECK OPTION</code> may be used to prevent + <code class="command">INSERT</code> and <code class="command">UPDATE</code> commands from creating + such rows that are not visible through the view. + </p><p> + If an automatically updatable view is marked with the + <code class="literal">security_barrier</code> property then all the view's <code class="literal">WHERE</code> + conditions (and any conditions using operators which are marked as <code class="literal">LEAKPROOF</code>) + will always be evaluated before any conditions that a user of the view has + added. See <a class="xref" href="rules-privileges.html" title="41.5. Rules and Privileges">Section 41.5</a> for full details. Note that, + due to this, rows which are not ultimately returned (because they do not + pass the user's <code class="literal">WHERE</code> conditions) may still end up being locked. + <code class="command">EXPLAIN</code> can be used to see which conditions are + applied at the relation level (and therefore do not lock rows) and which are + not. + </p><p> + 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 <code class="literal">INSTEAD OF</code> triggers on the view, which must + convert attempted inserts, etc. on the view into appropriate actions + on other tables. For more information see <a class="xref" href="sql-createtrigger.html" title="CREATE TRIGGER"><span class="refentrytitle">CREATE TRIGGER</span></a>. Another possibility is to create rules + (see <a class="xref" href="sql-createrule.html" title="CREATE RULE"><span class="refentrytitle">CREATE RULE</span></a>), but in practice triggers are + easier to understand and use correctly. + </p><p> + 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 <a class="xref" href="rules-privileges.html" title="41.5. Rules and Privileges">Section 41.5</a>). However, if the view has + <code class="literal">security_invoker</code> set to <code class="literal">true</code>, the + user performing the update, rather than the view owner, must have the + relevant privileges on the underlying base relations. + </p></div></div><div class="refsect1" id="id-1.9.3.97.8"><h2>Examples</h2><p> + Create a view consisting of all comedy films: + +</p><pre class="programlisting"> +CREATE VIEW comedies AS + SELECT * + FROM films + WHERE kind = 'Comedy'; +</pre><p> + This will create a view containing the columns that are in the + <code class="literal">film</code> table at the time of view creation. Though + <code class="literal">*</code> was used to create the view, columns added later to + the table will not be part of the view. + </p><p> + Create a view with <code class="literal">LOCAL CHECK OPTION</code>: + +</p><pre class="programlisting"> +CREATE VIEW universal_comedies AS + SELECT * + FROM comedies + WHERE classification = 'U' + WITH LOCAL CHECK OPTION; +</pre><p> + This will create a view based on the <code class="literal">comedies</code> view, showing + only films with <code class="literal">kind = 'Comedy'</code> and + <code class="literal">classification = 'U'</code>. Any attempt to <code class="command">INSERT</code> or + <code class="command">UPDATE</code> a row in the view will be rejected if the new row + doesn't have <code class="literal">classification = 'U'</code>, but the film + <code class="literal">kind</code> will not be checked. + </p><p> + Create a view with <code class="literal">CASCADED CHECK OPTION</code>: + +</p><pre class="programlisting"> +CREATE VIEW pg_comedies AS + SELECT * + FROM comedies + WHERE classification = 'PG' + WITH CASCADED CHECK OPTION; +</pre><p> + This will create a view that checks both the <code class="literal">kind</code> and + <code class="literal">classification</code> of new rows. + </p><p> + Create a view with a mix of updatable and non-updatable columns: + +</p><pre class="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'; +</pre><p> + This view will support <code class="command">INSERT</code>, <code class="command">UPDATE</code> and + <code class="command">DELETE</code>. All the columns from the <code class="literal">films</code> table will + be updatable, whereas the computed columns <code class="literal">country</code> and + <code class="literal">avg_rating</code> will be read-only. + </p><p> + Create a recursive view consisting of the numbers from 1 to 100: +</p><pre class="programlisting"> +CREATE RECURSIVE VIEW public.nums_1_100 (n) AS + VALUES (1) +UNION ALL + SELECT n+1 FROM nums_1_100 WHERE n < 100; +</pre><p> + Notice that although the recursive view's name is schema-qualified in this + <code class="command">CREATE</code>, its internal self-reference is not schema-qualified. + This is because the implicitly-created CTE's name cannot be + schema-qualified. + </p></div><div class="refsect1" id="id-1.9.3.97.9"><h2>Compatibility</h2><p> + <code class="command">CREATE OR REPLACE VIEW</code> is a + <span class="productname">PostgreSQL</span> language extension. + So is the concept of a temporary view. + The <code class="literal">WITH ( ... )</code> clause is an extension as well, as are + security barrier views and security invoker views. + </p></div><div class="refsect1" id="id-1.9.3.97.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterview.html" title="ALTER VIEW"><span class="refentrytitle">ALTER VIEW</span></a>, <a class="xref" href="sql-dropview.html" title="DROP VIEW"><span class="refentrytitle">DROP VIEW</span></a>, <a class="xref" href="sql-creatematerializedview.html" title="CREATE MATERIALIZED VIEW"><span class="refentrytitle">CREATE MATERIALIZED VIEW</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createusermapping.html" title="CREATE USER MAPPING">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-deallocate.html" title="DEALLOCATE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE USER MAPPING </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> DEALLOCATE</td></tr></table></div></body></html>
\ No newline at end of file |