doc/src/sgml/ref/set.sgml

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