Home • Explore • Blog • CI
☰



postgresql
2nd chunk of `doc/src/sgml/ref/create_view.sgml`
5f43ae92a607fb76b956e9bf8d123dff07edae6a434457100000000100000fa1
 <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>,
Title: CREATE VIEW Parameters: Defining View Attributes and Behavior
Summary
This section describes the parameters used in the CREATE VIEW command, including: the view's name, column names, and options like 'check_option' (local or cascaded), 'security_barrier' for row-level security, and 'security_invoker' to check user privileges instead of the view owner's. It also explains the SELECT or VALUES query that defines the view's columns and rows, and the WITH CHECK OPTION which controls the behavior of automatically updatable views during INSERT and UPDATE operations.