| 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 < 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> |