set.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/set.sgml,v 1.63 2002/08/04 03:53:11 momjian 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>
 <refsynopsisdiv>
  <synopsis>
SET [ SESSION | LOCAL ] <replaceable class="PARAMETER">variable</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>

  <refsect2 id="R2-SQL-SET-1">
   <title>Inputs</title>
   <para>
    <variablelist>

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

     <varlistentry>
      <term><option>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's
        executed outside a <command>BEGIN</> block, since the transaction
        will end immediately.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">variable</replaceable></term>
      <listitem>
       <para>
        A settable run-time parameter.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">value</replaceable></term>
      <listitem>
       <para>
        New value of parameter.  <option>DEFAULT</option> can be
        used to specify resetting the parameter to its default
        value. Lists of strings are allowed, but more complex
        constructs may need to be single or double quoted.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

 </refsynopsisdiv>
 
 <refsect1 id="R1-SQL-SET-1">
  <title>Description</title>

  <para>
   The <command>SET</command> command changes run-time configuration
   parameters.  Many of the run-time parameters listed in the
   <citetitle>Administrator's Guide</citetitle> 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.)
   Note that <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>

  <para>
   Here are additional details about a few of the parameters that can be set:

   <variablelist>
    <varlistentry>
     <term>DATESTYLE</term>
     <listitem>
      <para>
       Choose the date/time representation style. Two separate
       settings are involved: the default date/time output and the
       interpretation of ambiguous input.
      </para>

      <para>
       The following are date/time output styles:

       <variablelist>
        <varlistentry>
         <term>ISO</term>
         <listitem>
          <para>
           Use ISO 8601-style dates and times (<literal>YYYY-MM-DD
            HH:MM:SS</literal>). This is the default.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term>SQL</term>
         <listitem>
          <para>
           Use Oracle/Ingres-style dates and times. Note that this
           style has nothing to do with SQL (which mandates ISO 8601
           style), the naming of this option is a historical accident.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term>PostgreSQL</term>
         <listitem>
          <para>
           Use traditional <productname>PostgreSQL</productname> format.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term>German</term>
         <listitem>
          <para>
           Use <literal>dd.mm.yyyy</literal> for numeric date representations.
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
      </para>

      <para>
       The following two options determine both a substyle of the
       <quote>SQL</quote> and <quote>PostgreSQL</quote> output formats
       and the preferred interpretation of ambiguous date input.

       <variablelist>
        <varlistentry>
         <term>European</term>
         <listitem>
          <para>
           Use <literal>dd/mm/yyyy</literal> for numeric date representations.
          </para>
         </listitem>
        </varlistentry>

        <varlistentry>
         <term>NonEuropean</term>
         <term>US</term>
         <listitem>
          <para>
           Use <literal>mm/dd/yyyy</literal> for numeric date representations.
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
      </para>

      <para>
       A value for <command>SET DATESTYLE</command> can be one from
       the first list (output styles), or one from the second list
       (substyles), or one from each separated by a comma.
      </para>

      <para>
       <command>SET DATESTYLE</command> affects interpretation of
       input and provides several standard output formats. For
       applications needing different variations or tighter control
       over input or output, consider using
       the <function>to_char</function> family of
       functions.
      </para>

      <para>
       There are several now-deprecated means for setting the datestyle
       in addition to the normal methods of setting it via <command>SET</> or
       a configuration-file entry:
       <simplelist>
        <member>
         Setting the postmaster's <envar>PGDATESTYLE</envar> environment
         variable.  (This will be overridden by any of the other methods.)
        </member>
        <member>
         Running postmaster using the option <option>-o -e</option> to
         set dates to the <literal>European</literal> convention.
         (This overrides environment variables and configuration-file
         entries.)
        </member>
        <member>
         Setting the client's <envar>PGDATESTYLE</envar> environment variable.
         If PGDATESTYLE is set in the frontend environment of a client
         based on libpq, libpq will automatically set DATESTYLE to the
         value of PGDATESTYLE during connection start-up.  This is
         equivalent to a manually issued <command>SET DATESTYLE</>.
        </member>
       </simplelist>
      </para>

     </listitem>
    </varlistentry>

    <varlistentry>
     <term>NAMES</term>
     <listitem>
      <para>
       <command>SET NAMES</> is an alias for <command>SET CLIENT_ENCODING</>.
      </para>
     </listitem>
    </varlistentry>

     <varlistentry>
      <term>SEED</term>
      <listitem>
       <para>
       Sets the internal seed for the random number generator.
	
       <variablelist>
        <varlistentry>
         <term><replaceable class="parameter">value</replaceable></term>
         <listitem>
          <para>
           The value for the seed to be used by the
           <function>random</function> function. Allowed
           values are floating-point numbers between 0 and 1, which
           are then multiplied by 2<superscript>31</>-1.
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
      </para>

      <para>
       The seed can also be set by invoking the
       <function>setseed</function> SQL function:

       <programlisting>
SELECT setseed(<replaceable>value</replaceable>);
       </programlisting>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>SERVER_ENCODING</term>
     <listitem>
      <para>
       Shows the server-side multibyte encoding.  (At present, this
       parameter can be shown but not set, because the encoding is
       determined at initdb time.)
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>TIME ZONE</term>
     <term>TIMEZONE</term>
     <listitem>
      <para>
       Sets the default time zone for your session. Arguments can be
       an SQL time interval constant, an integer or double precision
       constant, or a string representing a time zone name recognized
       by the host operating system.
      </para>

      <para>
       Here are some typical values for time zone settings:

       <variablelist>
        <varlistentry>
         <term>'PST8PDT'</term>
         <listitem>
          <para>
           Set the time zone for Berkeley, California.
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term>'Portugal'</term>
         <listitem>
          <para>
           Set the time zone for Portugal.
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term>'Europe/Rome'</term>
         <listitem>
          <para>
           Set the time zone for Italy.
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term>7</term>
         <listitem>
          <para>
           Set the time zone to 7 hours offset west from GMT (equivalent
           to PDT).
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term>INTERVAL '08:00' HOUR TO MINUTE</term>
         <listitem>
          <para>
           Set the time zone to 8 hours offset west from GMT (equivalent
           to PST).
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
         <term>LOCAL</term>
         <term>DEFAULT</term>
         <listitem>
          <para>
           Set the time zone to your local time zone (the one that
           your operating system defaults to).
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
      </para>

      <para>
       The available time zone names depend on your operating
       system. For example, on Linux
       <filename>/usr/share/zoneinfo</filename> contains the database
       of time zones; the names of the files in that directory can be
       used as parameters to this command.
      </para>

      <para>
       If an invalid time zone is specified, the time zone
       becomes GMT (on most systems anyway).
      </para>

      <para>
       If the <envar>PGTZ</envar> environment variable is set in the frontend
       environment of a client based on libpq, libpq will automatically
       <command>SET TIMEZONE</command> to the value of
       <envar>PGTZ</envar> during connection start-up. 
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>

  <para>
   Use <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title"> to show the
   current setting of a parameter.
  </para>
  
 </refsect1>

 <refsect1>
  <title>Diagnostics</title>
  <para>
    
   <variablelist>
    <varlistentry>
     <term><computeroutput>SET</computeroutput></term>
     <listitem>
      <para>
       Message returned if successful.
      </para>
     </listitem>
    </varlistentry>
     
    <varlistentry>
     <term><computeroutput>ERROR:  not a valid option name: <replaceable>name</replaceable></computeroutput></term>
     <listitem>
      <para>
       The parameter you tried to set does not exist.
      </para>
     </listitem>
    </varlistentry>
     
    <varlistentry>
     <term><computeroutput>ERROR:  permission denied</computeroutput></term>
     <listitem>
      <para>
       You must be a superuser to alter certain settings.
      </para>
     </listitem>
    </varlistentry>
     
    <varlistentry>
     <term><computeroutput>ERROR:  <replaceable>name</replaceable> can only be set at start-up</computeroutput></term>
     <listitem>
      <para>
       Some parameters are fixed once the server is started.
      </para>
     </listitem>
    </varlistentry>
     
   </variablelist>
  </para>
 </refsect1>

 
 <refsect1>
  <title>Examples</title>
  <para>
   Set the style of date to traditional
   <productname>PostgreSQL</productname> with European conventions:
   <screen>
SET DATESTYLE TO PostgreSQL,European;
   </screen>
  </para>

  <para>
   Set the time zone for Berkeley, California, using quotes to
   preserve the uppercase spelling of the time zone name (note
   that the date style is <literal>PostgreSQL</literal> for this
   example): 

   <screen>
SET TIME ZONE 'PST8PDT';
SELECT CURRENT_TIMESTAMP AS today;
               today                
------------------------------------
 Tue Feb 26 07:32:21.42834 2002 PST
   </screen>
  </para>

  <para>
   Set the time zone for Italy (note the required single quotes to handle 
   the special characters):

   <screen>
SET TIME ZONE 'Europe/Rome';
SELECT CURRENT_TIMESTAMP AS today;
   
         today
------------------------
 1998-03-31 17:41:31+02
   </screen>
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-SET-3">
  <title>Compatibility</title>

  <refsect2 id="R2-SQL-SET-4">
   <title>
    SQL92
   </title>

   <para>
    <literal>SET TIME ZONE</literal>
    extends syntax defined in
    <acronym>SQL9x</acronym>. <acronym>SQL9x</acronym> allows
    only numeric time zone offsets while
    <productname>PostgreSQL</productname> allows full time zone
    specifier strings as well. All other <literal>SET</literal>
    features are
    <productname>PostgreSQL</productname> extensions.
   </para>
  </refsect2>
 </refsect1>

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

  <para>
    The function <function>set_config</function> provides the equivalent
    capability. See <citetitle>Miscellaneous Functions</citetitle> in the
    <citetitle>PostgreSQL User's Guide</citetitle>.
  </para>
 </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:
-->