Debian Codegraph @

doc/src/sgml/ref/create_view.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_view.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createview">
7 <indexterm zone="sql-createview">
8 <primary>CREATE VIEW</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE VIEW</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE VIEW</refname>
19 <refpurpose>define a new view</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW <replaceable class="parameter">name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
25 [ WITH ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] ) ]
26 AS <replaceable class="parameter">query</replaceable>
27 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
28 </synopsis>
29 </refsynopsisdiv>
30
31 <refsect1>
32 <title>Description</title>
33
34 <para>
35 <command>CREATE VIEW</command> defines a view of a query. The view
36 is not physically materialized. Instead, the query is run every time
37 the view is referenced in a query.
38 </para>
39
40 <para>
41 <command>CREATE OR REPLACE VIEW</command> is similar, but if a view
42 of the same name already exists, it is replaced. The new query must
43 generate the same columns that were generated by the existing view query
44 (that is, the same column names in the same order and with the same data
45 types), but it may add additional columns to the end of the list. The
46 calculations giving rise to the output columns may be completely different.
47 </para>
48
49 <para>
50 If a schema name is given (for example, <literal>CREATE VIEW
51 myschema.myview ...</literal>) then the view is created in the specified
52 schema. Otherwise it is created in the current schema. Temporary
53 views exist in a special schema, so a schema name cannot be given
54 when creating a temporary view. The name of the view must be
55 distinct from the name of any other relation (table, sequence, index, view,
56 materialized view, or foreign table) in the same schema.
57 </para>
58 </refsect1>
59
60 <refsect1>
61 <title>Parameters</title>
62
63 <variablelist>
64 <varlistentry>
65 <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
66 <listitem>
67 <para>
68 If specified, the view is created as a temporary view.
69 Temporary views are automatically dropped at the end of the
70 current session. Existing
71 permanent relations with the same name are not visible to the
72 current session while the temporary view exists, unless they are
73 referenced with schema-qualified names.
74 </para>
75
76 <para>
77 If any of the tables referenced by the view are temporary,
78 the view is created as a temporary view (whether
79 <literal>TEMPORARY</literal> is specified or not).
80 </para>
81 </listitem>
82 </varlistentry>
83
84 <varlistentry>
85 <term><literal>RECURSIVE</literal>
86 <indexterm zone="sql-createview">
87 <primary>RECURSIVE</primary>
88 <secondary>in views</secondary>
89 </indexterm>
90 </term>
91 <listitem>
92 <para>
93 Creates a recursive view. The syntax
94 <synopsis>
95 CREATE RECURSIVE VIEW [ <replaceable>schema</replaceable> . ] <replaceable>view_name</replaceable> (<replaceable>column_names</replaceable>) AS SELECT <replaceable>...</replaceable>;
96 </synopsis>
97 is equivalent to
98 <synopsis>
99 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>;
100 </synopsis>
101 A view column name list must be specified for a recursive view.
102 </para>
103 </listitem>
104 </varlistentry>
105
106 <varlistentry>
107 <term><replaceable class="parameter">name</replaceable></term>
108 <listitem>
109 <para>
110 The name (optionally schema-qualified) of a view to be created.
111 </para>
112 </listitem>
113 </varlistentry>
114
115 <varlistentry>
116 <term><replaceable class="parameter">column_name</replaceable></term>
117 <listitem>
118 <para>
119 An optional list of names to be used for columns of the view.
120 If not given, the column names are deduced from the query.
121 </para>
122 </listitem>
123 </varlistentry>
124
125 <varlistentry>
126 <term><literal>WITH ( <replaceable class="parameter">view_option_name</replaceable> [= <replaceable class="parameter">view_option_value</replaceable>] [, ... ] )</literal></term>
127 <listitem>
128 <para>
129 This clause specifies optional parameters for a view; the following
130 parameters are supported:
131
132 <variablelist>
133 <varlistentry>
134 <term><literal>check_option</literal> (<type>enum</type>)</term>
135 <listitem>
136 <para>
137 This parameter may be either <literal>local</literal> or
138 <literal>cascaded</literal>, and is equivalent to specifying
139 <literal>WITH [ CASCADED | LOCAL ] CHECK OPTION</literal> (see below).
140 </para>
141 </listitem>
142 </varlistentry>
143
144 <varlistentry>
145 <term><literal>security_barrier</literal> (<type>boolean</type>)</term>
146 <listitem>
147 <para>
148 This should be used if the view is intended to provide row-level
149 security. See <xref linkend="rules-privileges"/> for full details.
150 </para>
151 </listitem>
152 </varlistentry>
153
154 <varlistentry>
155 <term><literal>security_invoker</literal> (<type>boolean</type>)</term>
156 <listitem>
157 <para>
158 This option causes the underlying base relations to be checked
159 against the privileges of the user of the view rather than the view
160 owner. See the notes below for full details.
161 </para>
162 </listitem>
163 </varlistentry>
164 </variablelist>
165
166 All of the above options can be changed on existing views using <link
167 linkend="sql-alterview"><command>ALTER VIEW</command></link>.
168 </para>
169 </listitem>
170 </varlistentry>
171
172 <varlistentry>
173 <term><replaceable class="parameter">query</replaceable></term>
174 <listitem>
175 <para>
176 A <link linkend="sql-select"><command>SELECT</command></link> or
177 <link linkend="sql-values"><command>VALUES</command></link> command
178 which will provide the columns and rows of the view.
179 </para>
180 </listitem>
181 </varlistentry>
182
183 <varlistentry>
184 <term><literal>WITH [ CASCADED | LOCAL ] CHECK OPTION</literal>
185 <indexterm zone="sql-createview">
186 <primary>CHECK OPTION</primary>
187 </indexterm>
188 <indexterm zone="sql-createview">
189 <primary>WITH CHECK OPTION</primary>
190 </indexterm>
191 </term>
192 <listitem>
193 <para>
194 This option controls the behavior of automatically updatable views. When
195 this option is specified, <command>INSERT</command>,
196 <command>UPDATE</command>, and <command>MERGE</command>
197 commands on the view will be checked to ensure that new rows satisfy the
198 view-defining condition (that is, the new rows are checked to ensure that
199 they are visible through the view). If they are not, the update will be
200 rejected. If the <literal>CHECK OPTION</literal> is not specified,
201 <command>INSERT</command>, <command>UPDATE</command>, and
202 <command>MERGE</command> commands on the view are
203 allowed to create rows that are not visible through the view. The
204 following check options are supported:
205
206 <variablelist>
207 <varlistentry>
208 <term><literal>LOCAL</literal></term>
209 <listitem>
210 <para>
211 New rows are only checked against the conditions defined directly in
212 the view itself. Any conditions defined on underlying base views are
213 not checked (unless they also specify the <literal>CHECK OPTION</literal>).
214 </para>
215 </listitem>
216 </varlistentry>
217
218 <varlistentry>
219 <term><literal>CASCADED</literal></term>
220 <listitem>
221 <para>
222 New rows are checked against the conditions of the view and all
223 underlying base views. If the <literal>CHECK OPTION</literal> is specified,
224 and neither <literal>LOCAL</literal> nor <literal>CASCADED</literal> is specified,
225 then <literal>CASCADED</literal> is assumed.
226 </para>
227 </listitem>
228 </varlistentry>
229 </variablelist>
230 </para>
231
232 <para>
233 The <literal>CHECK OPTION</literal> may not be used with <literal>RECURSIVE</literal>
234 views.
235 </para>
236
237 <para>
238 Note that the <literal>CHECK OPTION</literal> is only supported on views that
239 are automatically updatable, and do not have <literal>INSTEAD OF</literal>
240 triggers or <literal>INSTEAD</literal> rules. If an automatically updatable
241 view is defined on top of a base view that has <literal>INSTEAD OF</literal>
242 triggers, then the <literal>LOCAL CHECK OPTION</literal> may be used to check
243 the conditions on the automatically updatable view, but the conditions
244 on the base view with <literal>INSTEAD OF</literal> triggers will not be
245 checked (a cascaded check option will not cascade down to a
246 trigger-updatable view, and any check options defined directly on a
247 trigger-updatable view will be ignored). If the view or any of its base
248 relations has an <literal>INSTEAD</literal> rule that causes the
249 <command>INSERT</command> or <command>UPDATE</command> command to be rewritten, then
250 all check options will be ignored in the rewritten query, including any
251 checks from automatically updatable views defined on top of the relation
252 with the <literal>INSTEAD</literal> rule. <command>MERGE</command> is not
253 supported if the view or any of its base relations have rules.
254 </para>
255 </listitem>
256 </varlistentry>
257 </variablelist>
258 </refsect1>
259
260 <refsect1>
261 <title>Notes</title>
262
263 <para>
264 Use the <link linkend="sql-dropview"><command>DROP VIEW</command></link>
265 statement to drop views.
266 </para>
267
268 <para>
269 Be careful that the names and types of the view's columns will be
270 assigned the way you want. For example:
271 <programlisting>
272 CREATE VIEW vista AS SELECT 'Hello World';
273 </programlisting>
274 is bad form because the column name defaults to <literal>?column?</literal>;
275 also, the column data type defaults to <type>text</type>, which might not
276 be what you wanted. Better style for a string literal in a view's
277 result is something like:
278 <programlisting>
279 CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
280 </programlisting>
281 </para>
282
283 <para>
284 By default, access to the underlying base relations referenced in the view
285 is determined by the permissions of the view owner. In some cases, this
286 can be used to provide secure but restricted access to the underlying
287 tables. However, not all views are secure against tampering; see <xref
288 linkend="rules-privileges"/> for details.
289 </para>
290
291 <para>
292 If the view has the <literal>security_invoker</literal> property set to
293 <literal>true</literal>, access to the underlying base relations is
294 determined by the permissions of the user executing the query, rather than
295 the view owner. Thus, the user of a security invoker view must have the
296 relevant permissions on the view and its underlying base relations.
297 </para>
298
299 <para>
300 If any of the underlying base relations is a security invoker view, it
301 will be treated as if it had been accessed directly from the original
302 query. Thus, a security invoker view will always check its underlying
303 base relations using the permissions of the current user, even if it is
304 accessed from a view without the <literal>security_invoker</literal>
305 property.
306 </para>
307
308 <para>
309 If any of the underlying base relations has
310 <link linkend="ddl-rowsecurity">row-level security</link> enabled, then
311 by default, the row-level security policies of the view owner are applied,
312 and access to any additional relations referred to by those policies is
313 determined by the permissions of the view owner. However, if the view has
314 <literal>security_invoker</literal> set to <literal>true</literal>, then
315 the policies and permissions of the invoking user are used instead, as if
316 the base relations had been referenced directly from the query using the
317 view.
318 </para>
319
320 <para>
321 Functions called in the view are treated the same as if they had been
322 called directly from the query using the view. Therefore, the user of
323 a view must have permissions to call all functions used by the view.
324 Functions in the view are executed with the privileges of the user
325 executing the query or the function owner, depending on whether the
326 functions are defined as <literal>SECURITY INVOKER</literal> or
327 <literal>SECURITY DEFINER</literal>. Thus, for example, calling
328 <literal>CURRENT_USER</literal> directly in a view will always return the
329 invoking user, not the view owner. This is not affected by the view's
330 <literal>security_invoker</literal> setting, and so a view with
331 <literal>security_invoker</literal> set to <literal>false</literal> is
332 <emphasis>not</emphasis> equivalent to a
333 <literal>SECURITY DEFINER</literal> function and those concepts should not
334 be confused.
335 </para>
336
337 <para>
338 The user creating or replacing a view must have <literal>USAGE</literal>
339 privileges on any schemas referred to in the view query, in order to look
340 up the referenced objects in those schemas. Note, however, that this
341 lookup only happens when the view is created or replaced. Therefore, the
342 user of the view only requires the <literal>USAGE</literal> privilege on
343 the schema containing the view, not on the schemas referred to in the view
344 query, even for a security invoker view.
345 </para>
346
347 <para>
348 When <command>CREATE OR REPLACE VIEW</command> is used on an existing
349 view, only the view's defining SELECT rule, plus any
350 <literal>WITH ( ... )</literal> parameters and its
351 <literal>CHECK OPTION</literal> are changed.
352 Other view properties, including ownership, permissions, and non-SELECT
353 rules, remain unchanged. You must own the view
354 to replace it (this includes being a member of the owning role).
355 </para>
356
357 <refsect2 id="sql-createview-updatable-views">
358 <title>Updatable Views</title>
359
360 <indexterm zone="sql-createview-updatable-views">
361 <primary>updatable views</primary>
362 </indexterm>
363
364 <para>
365 Simple views are automatically updatable: the system will allow
366 <command>INSERT</command>, <command>UPDATE</command>,
367 <command>DELETE</command>, and <command>MERGE</command> statements
368 to be used on the view in the same way as on a regular table. A view is
369 automatically updatable if it satisfies all of the following conditions:
370
371 <itemizedlist>
372 <listitem>
373 <para>
374 The view must have exactly one entry in its <literal>FROM</literal> list,
375 which must be a table or another updatable view.
376 </para>
377 </listitem>
378
379 <listitem>
380 <para>
381 The view definition must not contain <literal>WITH</literal>,
382 <literal>DISTINCT</literal>, <literal>GROUP BY</literal>, <literal>HAVING</literal>,
383 <literal>LIMIT</literal>, or <literal>OFFSET</literal> clauses at the top level.
384 </para>
385 </listitem>
386
387 <listitem>
388 <para>
389 The view definition must not contain set operations (<literal>UNION</literal>,
390 <literal>INTERSECT</literal> or <literal>EXCEPT</literal>) at the top level.
391 </para>
392 </listitem>
393
394 <listitem>
395 <para>
396 The view's select list must not contain any aggregates, window functions
397 or set-returning functions.
398 </para>
399 </listitem>
400 </itemizedlist>
401 </para>
402
403 <para>
404 An automatically updatable view may contain a mix of updatable and
405 non-updatable columns. A column is updatable if it is a simple reference
406 to an updatable column of the underlying base relation; otherwise the
407 column is read-only, and an error will be raised if an
408 <command>INSERT</command>, <command>UPDATE</command>, or
409 <command>MERGE</command> statement attempts to assign a value to it.
410 </para>
411
412 <para>
413 If the view is automatically updatable the system will convert any
414 <command>INSERT</command>, <command>UPDATE</command>,
415 <command>DELETE</command>, or <command>MERGE</command> statement
416 on the view into the corresponding statement on the underlying base
417 relation. <command>INSERT</command> statements that have an <literal>ON
418 CONFLICT UPDATE</literal> clause are fully supported.
419 </para>
420
421 <para>
422 If an automatically updatable view contains a <literal>WHERE</literal>
423 condition, the condition restricts which rows of the base relation are
424 available to be modified by <command>UPDATE</command>,
425 <command>DELETE</command>, and <command>MERGE</command>
426 statements on the view. However, an <command>UPDATE</command> or
427 <command>MERGE</command> is allowed to
428 change a row so that it no longer satisfies the <literal>WHERE</literal>
429 condition, and thus is no longer visible through the view. Similarly,
430 an <command>INSERT</command> or <command>MERGE</command> command can
431 potentially insert base-relation rows
432 that do not satisfy the <literal>WHERE</literal> condition and thus are not
433 visible through the view (<literal>ON CONFLICT UPDATE</literal> may
434 similarly affect an existing row not visible through the view).
435 The <literal>CHECK OPTION</literal> may be used to prevent
436 <command>INSERT</command>, <command>UPDATE</command>, and
437 <command>MERGE</command> commands from creating such rows that are not
438 visible through the view.
439 </para>
440
441 <para>
442 If an automatically updatable view is marked with the
443 <literal>security_barrier</literal> property then all the view's <literal>WHERE</literal>
444 conditions (and any conditions using operators which are marked as <literal>LEAKPROOF</literal>)
445 will always be evaluated before any conditions that a user of the view has
446 added. See <xref linkend="rules-privileges"/> for full details. Note that,
447 due to this, rows which are not ultimately returned (because they do not
448 pass the user's <literal>WHERE</literal> conditions) may still end up being locked.
449 <command>EXPLAIN</command> can be used to see which conditions are
450 applied at the relation level (and therefore do not lock rows) and which are
451 not.
452 </para>
453
454 <para>
455 A more complex view that does not satisfy all these conditions is
456 read-only by default: the system will not allow an <command>INSERT</command>,
457 <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>
458 on the view. You can get the effect of an updatable view by
459 creating <literal>INSTEAD OF</literal> triggers on the view, which must
460 convert attempted inserts, etc. on the view into appropriate actions
461 on other tables. For more information see <xref
462 linkend="sql-createtrigger"/>. Another possibility is to create rules
463 (see <xref linkend="sql-createrule"/>), but in practice triggers are
464 easier to understand and use correctly. Also note that <command>MERGE</command>
465 is not supported on relations with rules.
466 </para>
467
468 <para>
469 Note that the user performing the insert, update or delete on the view
470 must have the corresponding insert, update or delete privilege on the
471 view. In addition, by default, the view's owner must have the relevant
472 privileges on the underlying base relations, whereas the user performing
473 the update does not need any permissions on the underlying base relations
474 (see <xref linkend="rules-privileges"/>). However, if the view has
475 <literal>security_invoker</literal> set to <literal>true</literal>, the
476 user performing the update, rather than the view owner, must have the
477 relevant privileges on the underlying base relations.
478 </para>
479 </refsect2>
480 </refsect1>
481
482 <refsect1>
483 <title>Examples</title>
484
485 <para>
486 Create a view consisting of all comedy films:
487
488 <programlisting>
489 CREATE VIEW comedies AS
490 SELECT *
491 FROM films
492 WHERE kind = 'Comedy';
493 </programlisting>
494 This will create a view containing the columns that are in the
495 <literal>film</literal> table at the time of view creation. Though
496 <literal>*</literal> was used to create the view, columns added later to
497 the table will not be part of the view.
498 </para>
499
500 <para>
501 Create a view with <literal>LOCAL CHECK OPTION</literal>:
502
503 <programlisting>
504 CREATE VIEW universal_comedies AS
505 SELECT *
506 FROM comedies
507 WHERE classification = 'U'
508 WITH LOCAL CHECK OPTION;
509 </programlisting>
510 This will create a view based on the <literal>comedies</literal> view, showing
511 only films with <literal>kind = 'Comedy'</literal> and
512 <literal>classification = 'U'</literal>. Any attempt to <command>INSERT</command> or
513 <command>UPDATE</command> a row in the view will be rejected if the new row
514 doesn't have <literal>classification = 'U'</literal>, but the film
515 <literal>kind</literal> will not be checked.
516 </para>
517
518 <para>
519 Create a view with <literal>CASCADED CHECK OPTION</literal>:
520
521 <programlisting>
522 CREATE VIEW pg_comedies AS
523 SELECT *
524 FROM comedies
525 WHERE classification = 'PG'
526 WITH CASCADED CHECK OPTION;
527 </programlisting>
528 This will create a view that checks both the <literal>kind</literal> and
529 <literal>classification</literal> of new rows.
530 </para>
531
532 <para>
533 Create a view with a mix of updatable and non-updatable columns:
534
535 <programlisting>
536 CREATE VIEW comedies AS
537 SELECT f.*,
538 country_code_to_name(f.country_code) AS country,
539 (SELECT avg(r.rating)
540 FROM user_ratings r
541 WHERE r.film_id = f.id) AS avg_rating
542 FROM films f
543 WHERE f.kind = 'Comedy';
544 </programlisting>
545 This view will support <command>INSERT</command>, <command>UPDATE</command> and
546 <command>DELETE</command>. All the columns from the <literal>films</literal> table will
547 be updatable, whereas the computed columns <literal>country</literal> and
548 <literal>avg_rating</literal> will be read-only.
549 </para>
550
551 <para>
552 Create a recursive view consisting of the numbers from 1 to 100:
553 <programlisting>
554 CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
555 VALUES (1)
556 UNION ALL
557 SELECT n+1 FROM nums_1_100 WHERE n &lt; 100;
558 </programlisting>
559 Notice that although the recursive view's name is schema-qualified in this
560 <command>CREATE</command>, its internal self-reference is not schema-qualified.
561 This is because the implicitly-created CTE's name cannot be
562 schema-qualified.
563 </para>
564 </refsect1>
565
566 <refsect1>
567 <title>Compatibility</title>
568
569 <para>
570 <command>CREATE OR REPLACE VIEW</command> is a
571 <productname>PostgreSQL</productname> language extension.
572 So is the concept of a temporary view.
573 The <literal>WITH ( ... )</literal> clause is an extension as well, as are
574 security barrier views and security invoker views.
575 </para>
576 </refsect1>
577
578 <refsect1>
579 <title>See Also</title>
580
581 <simplelist type="inline">
582 <member><xref linkend="sql-alterview"/></member>
583 <member><xref linkend="sql-dropview"/></member>
584 <member><xref linkend="sql-creatematerializedview"/></member>
585 </simplelist>
586 </refsect1>
587 </refentry>