doc/src/sgml/ref/set.sgml

<!--
$PostgreSQL: pgsql/doc/src/sgml/ref/set.sgml,v 1.94 2008/11/14 10:22:47 petere Exp $
PostgreSQL documentation
-->

<refentry id="SQL-SET">
 <refmeta>
  <refentrytitle id="SQL-SET-TITLE">SET</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>SET</refname>
  <refpurpose>change a run-time parameter</refpurpose>
 </refnamediv>

 <indexterm zone="sql-set">
  <primary>SET</primary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
SET [ SESSION | LOCAL ] <replaceable class="PARAMETER">configuration_parameter</replaceable> { TO | = } { <replaceable class="PARAMETER">value</replaceable> | '<replaceable class="PARAMETER">value</replaceable>' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { <replaceable class="PARAMETER">timezone</replaceable> | LOCAL | DEFAULT }
</synopsis>
 </refsynopsisdiv>
 
 <refsect1>
  <title>Description</title>

  <para>
   The <command>SET</command> command changes run-time configuration
   parameters.  Many of the run-time parameters listed in
   <xref linkend="runtime-config"> can be changed on-the-fly with
   <command>SET</command>.
   (But some require superuser privileges to change, and others cannot
   be changed after server or session start.)
   <command>SET</command> only affects the value used by the current
   session.
  </para>

  <para>
   If <command>SET</command> (or equivalently <command>SET SESSION</command>)
   is issued within a transaction that is later aborted, the effects of the
   <command>SET</command> command disappear when the transaction is rolled
   back.  Once the surrounding transaction is committed, the effects
   will persist until the end of the session, unless overridden by another
   <command>SET</command>.
  </para>

  <para>
   The effects of <command>SET LOCAL</command> last only till the end of
   the current transaction, whether committed or not.  A special case is
   <command>SET</command> followed by <command>SET LOCAL</command> within
   a single transaction: the <command>SET LOCAL</command> value will be
   seen until the end of the transaction, but afterwards (if the transaction
   is committed) the <command>SET</command> value will take effect.
  </para>

  <para>
   The effects of <command>SET</command> or <command>SET LOCAL</command> are
   also canceled by rolling back to a savepoint that is earlier than the
   command.
  </para>

  <para>
   If <command>SET LOCAL</command> is used within a function that has a
   <literal>SET</> option for the same variable (see
   <xref linkend="sql-createfunction" endterm="sql-createfunction-title">),
   the effects of the <command>SET LOCAL</command> command disappear at
   function exit; that is, the value in effect when the function was called is
   restored anyway.  This allows <command>SET LOCAL</command> to be used for
   dynamic or repeated changes of a parameter within a function, while still
   having the convenience of using the <literal>SET</> option to save and
   restore the caller's value.  However, a regular <command>SET</> command
   overrides any surrounding function's <literal>SET</> option; its effects
   will persist unless rolled back.
  </para>

  <note>
   <para>
    In <productname>PostgreSQL</productname> versions 8.0 through 8.2,
    the effects of a <command>SET LOCAL</command> would be canceled by
    releasing an earlier savepoint, or by successful exit from a
    <application>PL/pgSQL</application> exception block.  This behavior
    has been changed because it was deemed unintuitive.
   </para>
  </note>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><literal>SESSION</></term>
    <listitem>
     <para>
      Specifies that the command takes effect for the current session.
      (This is the default if neither <literal>SESSION</> nor
      <literal>LOCAL</> appears.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>LOCAL</></term>
    <listitem>
     <para>
      Specifies that the command takes effect for only the current
      transaction.  After <command>COMMIT</> or <command>ROLLBACK</>,
      the session-level setting takes effect again.  Note that
      <command>SET LOCAL</> will appear to have no effect if it is
      executed outside a <command>BEGIN</> block, since the
      transaction will end immediately.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="PARAMETER">configuration_parameter</replaceable></term>
    <listitem>
     <para>
      Name of a settable run-time parameter.  Available parameters are
      documented in <xref linkend="runtime-config"> and below.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="PARAMETER">value</replaceable></term>
    <listitem>
     <para>
      New value of parameter.  Values can be specified as string
      constants, identifiers, numbers, or comma-separated lists of
      these, as appropriate for the particular parameter.
      <literal>DEFAULT</literal> can be written to specify
      resetting the parameter to its default value (that is, whatever
      value it would have had if no <command>SET</> had been executed
      in the current session).
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

  <para>
   Besides the configuration parameters documented in <xref
   linkend="runtime-config">, there are a few that can only be
   adjusted using the <command>SET</command> command or that have a
   special syntax:

   <variablelist>
    <varlistentry>
     <term><literal>SCHEMA</literal></term>
     <listitem>
      <para>
       <literal>SET SCHEMA '<replaceable>value</>'</> is an alias for
       <literal>SET search_path TO <replaceable>value</></>.  Only one
       schema can be specified using this syntax.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>NAMES</literal></term>
     <listitem>
      <para>
       <literal>SET NAMES <replaceable>value</></> is an alias for
       <literal>SET client_encoding TO <replaceable>value</></>.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>SEED</literal></term>
     <listitem>
      <para>
       Sets the internal seed for the random number generator (the
       function <function>random</function>).  Allowed values are
       floating-point numbers between -1 and 1, which are then
       multiplied by 2<superscript>31</>-1.
      </para>

      <para>
       The seed can also be set by invoking the function
       <function>setseed</function>:
<programlisting>
SELECT setseed(<replaceable>value</replaceable>);
</programlisting>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>TIME ZONE</literal></term>
     <listitem>
      <para>
       <literal>SET TIME ZONE <replaceable>value</></> is an alias
       for <literal>SET timezone TO <replaceable>value</></>.  The
       syntax <literal>SET TIME ZONE</literal> allows special syntax
       for the time zone specification.  Here are examples of valid
       values:

       <variablelist>
        <varlistentry>
         <term><literal>'PST8PDT'</literal></term>
         <listitem>
          <para>
           The time zone for Berkeley, California.
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term><literal>'Europe/Rome'</literal></term>
         <listitem>
          <para>
           The time zone for Italy.
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term><literal>-7</literal></term>
         <listitem>
          <para>
           The time zone 7 hours west from UTC (equivalent
           to PDT).  Positive values are east from UTC.
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term><literal>INTERVAL '-08:00' HOUR TO MINUTE</literal></term>
         <listitem>
          <para>
           The time zone 8 hours west from UTC (equivalent
           to PST).
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term><literal>LOCAL</literal></term>
         <term><literal>DEFAULT</literal></term>
         <listitem>
          <para>
           Set the time zone to your local time zone (that is, the
           server's default value of <varname>timezone</>; if this
           has not been explicitly set anywhere, it will be the zone that
           the server's operating system defaults to).
          </para>
         </listitem>
        </varlistentry>
       </variablelist>

       See <xref linkend="datatype-timezones"> for more information
       about time zones.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   The function <function>set_config</function> provides equivalent
   functionality; see <xref linkend="functions-admin">.
   Also, it is possible to UPDATE the
   <link linkend="view-pg-settings"><structname>pg_settings</structname></link>
   system view to perform the equivalent of <command>SET</>.
  </para>
 </refsect1>
 
 <refsect1>
  <title>Examples</title>

  <para>
   Set the schema search path:
<programlisting>
SET search_path TO my_schema, public;
</programlisting>
  </para>

  <para>
   Set the style of date to traditional
   <productname>POSTGRES</productname> with <quote>day before month</>
   input convention:
<screen>
SET datestyle TO postgres, dmy;
</screen>
  </para>

  <para>
   Set the time zone for Berkeley, California:
<screen>
SET TIME ZONE 'PST8PDT';
</screen>
  </para>

  <para>
   Set the time zone for Italy:
<screen>
SET TIME ZONE 'Europe/Rome';
</screen>
  </para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <literal>SET TIME ZONE</literal> extends syntax defined in the SQL
   standard.  The standard allows only numeric time zone offsets while
   <productname>PostgreSQL</productname> allows more flexible
   time-zone specifications.  All other <literal>SET</literal>
   features are <productname>PostgreSQL</productname> extensions.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="SQL-RESET" endterm="SQL-RESET-title"></member>
   <member><xref linkend="SQL-SHOW" endterm="SQL-SHOW-title"></member>
  </simplelist>
 </refsect1>
</refentry>