doc/src/sgml/ref/set.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-SET">
   7  <refmeta>
   8   <refentrytitle id="SQL-SET-TITLE">SET</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>SET</refname>
  15   <refpurpose>change a run-time parameter</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-set">
  19   <primary>SET</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 SET [ SESSION | LOCAL ] <replaceable class="PARAMETER">configuration_parameter</replaceable> { TO | = } { <replaceable class="PARAMETER">value</replaceable> | '<replaceable class="PARAMETER">value</replaceable>' | DEFAULT }
  25 SET [ SESSION | LOCAL ] TIME ZONE { <replaceable class="PARAMETER">timezone</replaceable> | LOCAL | DEFAULT }
  26 </synopsis>
  27  </refsynopsisdiv>
  28
  29  <refsect1>
  30   <title>Description</title>
  31
  32   <para>
  33    The <command>SET</command> command changes run-time configuration
  34    parameters.  Many of the run-time parameters listed in
  35    <xref linkend="runtime-config"> can be changed on-the-fly with
  36    <command>SET</command>.
  37    (But some require superuser privileges to change, and others cannot
  38    be changed after server or session start.)
  39    <command>SET</command> only affects the value used by the current
  40    session.
  41   </para>
  42
  43   <para>
  44    If <command>SET</command> (or equivalently <command>SET SESSION</command>)
  45    is issued within a transaction that is later aborted, the effects of the
  46    <command>SET</command> command disappear when the transaction is rolled
  47    back.  Once the surrounding transaction is committed, the effects
  48    will persist until the end of the session, unless overridden by another
  49    <command>SET</command>.
  50   </para>
  51
  52   <para>
  53    The effects of <command>SET LOCAL</command> last only till the end of
  54    the current transaction, whether committed or not.  A special case is
  55    <command>SET</command> followed by <command>SET LOCAL</command> within
  56    a single transaction: the <command>SET LOCAL</command> value will be
  57    seen until the end of the transaction, but afterwards (if the transaction
  58    is committed) the <command>SET</command> value will take effect.
  59   </para>
  60
  61   <para>
  62    The effects of <command>SET</command> or <command>SET LOCAL</command> are
  63    also canceled by rolling back to a savepoint that is earlier than the
  64    command.
  65   </para>
  66
  67   <para>
  68    If <command>SET LOCAL</command> is used within a function that has a
  69    <literal>SET</> option for the same variable (see
  70    <xref linkend="sql-createfunction" endterm="sql-createfunction-title">),
  71    the effects of the <command>SET LOCAL</command> command disappear at
  72    function exit; that is, the value in effect when the function was called is
  73    restored anyway.  This allows <command>SET LOCAL</command> to be used for
  74    dynamic or repeated changes of a parameter within a function, while still
  75    having the convenience of using the <literal>SET</> option to save and
  76    restore the caller's value.  However, a regular <command>SET</> command
  77    overrides any surrounding function's <literal>SET</> option; its effects
  78    will persist unless rolled back.
  79   </para>
  80
  81   <note>
  82    <para>
  83     In <productname>PostgreSQL</productname> versions 8.0 through 8.2,
  84     the effects of a <command>SET LOCAL</command> would be canceled by
  85     releasing an earlier savepoint, or by successful exit from a
  86     <application>PL/pgSQL</application> exception block.  This behavior
  87     has been changed because it was deemed unintuitive.
  88    </para>
  89   </note>
  90  </refsect1>
  91
  92  <refsect1>
  93   <title>Parameters</title>
  94
  95   <variablelist>
  96    <varlistentry>
  97     <term><literal>SESSION</></term>
  98     <listitem>
  99      <para>
 100       Specifies that the command takes effect for the current session.
 101       (This is the default if neither <literal>SESSION</> nor
 102       <literal>LOCAL</> appears.)
 103      </para>
 104     </listitem>
 105    </varlistentry>
 106
 107    <varlistentry>
 108     <term><literal>LOCAL</></term>
 109     <listitem>
 110      <para>
 111       Specifies that the command takes effect for only the current
 112       transaction.  After <command>COMMIT</> or <command>ROLLBACK</>,
 113       the session-level setting takes effect again.  Note that
 114       <command>SET LOCAL</> will appear to have no effect if it is
 115       executed outside a <command>BEGIN</> block, since the
 116       transaction will end immediately.
 117      </para>
 118     </listitem>
 119    </varlistentry>
 120
 121    <varlistentry>
 122     <term><replaceable class="PARAMETER">configuration_parameter</replaceable></term>
 123     <listitem>
 124      <para>
 125       Name of a settable run-time parameter.  Available parameters are
 126       documented in <xref linkend="runtime-config"> and below.
 127      </para>
 128     </listitem>
 129    </varlistentry>
 130
 131    <varlistentry>
 132     <term><replaceable class="PARAMETER">value</replaceable></term>
 133     <listitem>
 134      <para>
 135       New value of parameter.  Values can be specified as string
 136       constants, identifiers, numbers, or comma-separated lists of
 137       these, as appropriate for the particular parameter.
 138       <literal>DEFAULT</literal> can be written to specify
 139       resetting the parameter to its default value (that is, whatever
 140       value it would have had if no <command>SET</> had been executed
 141       in the current session).
 142      </para>
 143     </listitem>
 144    </varlistentry>
 145   </variablelist>
 146
 147   <para>
 148    Besides the configuration parameters documented in <xref
 149    linkend="runtime-config">, there are a few that can only be
 150    adjusted using the <command>SET</command> command or that have a
 151    special syntax:
 152
 153    <variablelist>
 154     <varlistentry>
 155      <term><literal>SCHEMA</literal></term>
 156      <listitem>
 157       <para>
 158        <literal>SET SCHEMA '<replaceable>value</>'</> is an alias for
 159        <literal>SET search_path TO <replaceable>value</></>.  Only one
 160        schema can be specified using this syntax.
 161       </para>
 162      </listitem>
 163     </varlistentry>
 164
 165     <varlistentry>
 166      <term><literal>NAMES</literal></term>
 167      <listitem>
 168       <para>
 169        <literal>SET NAMES <replaceable>value</></> is an alias for
 170        <literal>SET client_encoding TO <replaceable>value</></>.
 171       </para>
 172      </listitem>
 173     </varlistentry>
 174
 175     <varlistentry>
 176      <term><literal>SEED</literal></term>
 177      <listitem>
 178       <para>
 179        Sets the internal seed for the random number generator (the
 180        function <function>random</function>).  Allowed values are
 181        floating-point numbers between -1 and 1, which are then
 182        multiplied by 2<superscript>31</>-1.
 183       </para>
 184
 185       <para>
 186        The seed can also be set by invoking the function
 187        <function>setseed</function>:
 188 <programlisting>
 189 SELECT setseed(<replaceable>value</replaceable>);
 190 </programlisting>
 191       </para>
 192      </listitem>
 193     </varlistentry>
 194
 195     <varlistentry>
 196      <term><literal>TIME ZONE</literal></term>
 197      <listitem>
 198       <para>
 199        <literal>SET TIME ZONE <replaceable>value</></> is an alias
 200        for <literal>SET timezone TO <replaceable>value</></>.  The
 201        syntax <literal>SET TIME ZONE</literal> allows special syntax
 202        for the time zone specification.  Here are examples of valid
 203        values:
 204
 205        <variablelist>
 206         <varlistentry>
 207          <term><literal>'PST8PDT'</literal></term>
 208          <listitem>
 209           <para>
 210            The time zone for Berkeley, California.
 211           </para>
 212          </listitem>
 213         </varlistentry>
 214         <varlistentry>
 215          <term><literal>'Europe/Rome'</literal></term>
 216          <listitem>
 217           <para>
 218            The time zone for Italy.
 219           </para>
 220          </listitem>
 221         </varlistentry>
 222         <varlistentry>
 223          <term><literal>-7</literal></term>
 224          <listitem>
 225           <para>
 226            The time zone 7 hours west from UTC (equivalent
 227            to PDT).  Positive values are east from UTC.
 228           </para>
 229          </listitem>
 230         </varlistentry>
 231         <varlistentry>
 232          <term><literal>INTERVAL '-08:00' HOUR TO MINUTE</literal></term>
 233          <listitem>
 234           <para>
 235            The time zone 8 hours west from UTC (equivalent
 236            to PST).
 237           </para>
 238          </listitem>
 239         </varlistentry>
 240         <varlistentry>
 241          <term><literal>LOCAL</literal></term>
 242          <term><literal>DEFAULT</literal></term>
 243          <listitem>
 244           <para>
 245            Set the time zone to your local time zone (that is, the
 246            server's default value of <varname>timezone</>; if this
 247            has not been explicitly set anywhere, it will be the zone that
 248            the server's operating system defaults to).
 249           </para>
 250          </listitem>
 251         </varlistentry>
 252        </variablelist>
 253
 254        See <xref linkend="datatype-timezones"> for more information
 255        about time zones.
 256       </para>
 257      </listitem>
 258     </varlistentry>
 259    </variablelist>
 260   </para>
 261  </refsect1>
 262
 263  <refsect1>
 264   <title>Notes</title>
 265
 266   <para>
 267    The function <function>set_config</function> provides equivalent
 268    functionality; see <xref linkend="functions-admin">.
 269    Also, it is possible to UPDATE the
 270    <link linkend="view-pg-settings"><structname>pg_settings</structname></link>
 271    system view to perform the equivalent of <command>SET</>.
 272   </para>
 273  </refsect1>
 274
 275  <refsect1>
 276   <title>Examples</title>
 277
 278   <para>
 279    Set the schema search path:
 280 <programlisting>
 281 SET search_path TO my_schema, public;
 282 </programlisting>
 283   </para>
 284
 285   <para>
 286    Set the style of date to traditional
 287    <productname>POSTGRES</productname> with <quote>day before month</>
 288    input convention:
 289 <screen>
 290 SET datestyle TO postgres, dmy;
 291 </screen>
 292   </para>
 293
 294   <para>
 295    Set the time zone for Berkeley, California:
 296 <screen>
 297 SET TIME ZONE 'PST8PDT';
 298 </screen>
 299   </para>
 300
 301   <para>
 302    Set the time zone for Italy:
 303 <screen>
 304 SET TIME ZONE 'Europe/Rome';
 305 </screen>
 306   </para>
 307  </refsect1>
 308
 309  <refsect1>
 310   <title>Compatibility</title>
 311
 312   <para>
 313    <literal>SET TIME ZONE</literal> extends syntax defined in the SQL
 314    standard.  The standard allows only numeric time zone offsets while
 315    <productname>PostgreSQL</productname> allows more flexible
 316    time-zone specifications.  All other <literal>SET</literal>
 317    features are <productname>PostgreSQL</productname> extensions.
 318   </para>
 319  </refsect1>
 320
 321  <refsect1>
 322   <title>See Also</title>
 323
 324   <simplelist type="inline">
 325    <member><xref linkend="SQL-RESET" endterm="SQL-RESET-title"></member>
 326    <member><xref linkend="SQL-SHOW" endterm="SQL-SHOW-title"></member>
 327   </simplelist>
 328  </refsect1>
 329 </refentry>