path: root/doc/src/sgml/ref/set.sgml

<!--
$PostgreSQL: pgsql/doc/src/sgml/ref/set.sgml,v 1.86 2004/08/10 00:55:08 tgl Exp $
PostgreSQL documentation
-->

<refentry id="SQL-SET">
 <refmeta>
  <refentrytitle id="SQL-SET-TITLE">SET</refentrytitle>
  <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">name</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 <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.  (This behavior represents a change from
   <productname>PostgreSQL</productname> versions prior to 7.3, where
   the effects of <command>SET</command> would not roll back after a later
   error.)  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>
 </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">name</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.  <literal>DEFAULT</literal> can be used to specify
      resetting the parameter to its default value.
     </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>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 0 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 (the one that
           the server's operating system defaults to).
          </para>
         </listitem>
        </varlistentry>
       </variablelist>

       See <xref linkend="datatype-datetime"> for more information
       about time zones.  Also, <xref linkend="datetime-appendix">
       has a list of the recognized names for 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">.
  </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>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->