set.sgml 10.2 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ref/set.sgml,v 1.93 2008/10/27 09:37:47 petere Exp $
3
PostgreSQL documentation
4 5
-->

6 7
<refentry id="SQL-SET">
 <refmeta>
8
  <refentrytitle id="SQL-SET-TITLE">SET</refentrytitle>
9 10
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
11

12
 <refnamediv>
13
  <refname>SET</refname>
14
  <refpurpose>change a run-time parameter</refpurpose>
15
 </refnamediv>
16

Peter Eisentraut's avatar
Peter Eisentraut committed
17 18 19 20
 <indexterm zone="sql-set">
  <primary>SET</primary>
 </indexterm>

21
 <refsynopsisdiv>
22
<synopsis>
23
SET [ SESSION | LOCAL ] <replaceable class="PARAMETER">configuration_parameter</replaceable> { TO | = } { <replaceable class="PARAMETER">value</replaceable> | '<replaceable class="PARAMETER">value</replaceable>' | DEFAULT }
24
SET [ SESSION | LOCAL ] TIME ZONE { <replaceable class="PARAMETER">timezone</replaceable> | LOCAL | DEFAULT }
25
</synopsis>
26 27
 </refsynopsisdiv>
 
28
 <refsect1>
29
  <title>Description</title>
30

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

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

51 52 53 54 55 56 57 58
  <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>
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

  <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>
89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
 </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>
121
    <term><replaceable class="PARAMETER">configuration_parameter</replaceable></term>
122 123 124 125 126 127 128 129 130 131 132 133 134 135
    <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
136 137 138 139 140
      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).
141 142 143 144
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
145

146
  <para>
147 148 149 150
   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:
151 152

   <variablelist>
153 154 155 156 157 158 159 160 161 162 163
    <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>

164
    <varlistentry>
165
     <term><literal>NAMES</literal></term>
166 167
     <listitem>
      <para>
Tom Lane's avatar
Tom Lane committed
168
       <literal>SET NAMES <replaceable>value</></> is an alias for
169
       <literal>SET client_encoding TO <replaceable>value</></>.
170 171 172 173 174
      </para>
     </listitem>
    </varlistentry>

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

184
      <para>
185 186 187
       The seed can also be set by invoking the function
       <function>setseed</function>:
<programlisting>
188
SELECT setseed(<replaceable>value</replaceable>);
189
</programlisting>
190 191 192
      </para>
     </listitem>
    </varlistentry>
193

194
    <varlistentry>
195
     <term><literal>TIME ZONE</literal></term>
196
     <listitem>
197
      <para>
Tom Lane's avatar
Tom Lane committed
198
       <literal>SET TIME ZONE <replaceable>value</></> is an alias
199
       for <literal>SET timezone TO <replaceable>value</></>.  The
Tom Lane's avatar
Tom Lane committed
200
       syntax <literal>SET TIME ZONE</literal> allows special syntax
201
       for the time zone specification.  Here are examples of valid
202
       values:
203 204

       <variablelist>
205
        <varlistentry>
206
         <term><literal>'PST8PDT'</literal></term>
207 208
         <listitem>
          <para>
209
           The time zone for Berkeley, California.
210 211 212 213
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
214
         <term><literal>'Europe/Rome'</literal></term>
215 216
         <listitem>
          <para>
217
           The time zone for Italy.
218 219 220 221
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
222
         <term><literal>-7</literal></term>
223 224
         <listitem>
          <para>
Tom Lane's avatar
Tom Lane committed
225
           The time zone 7 hours west from UTC (equivalent
226
           to PDT).  Positive values are east from UTC.
227 228 229 230
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
231
         <term><literal>INTERVAL '-08:00' HOUR TO MINUTE</literal></term>
232 233
         <listitem>
          <para>
Tom Lane's avatar
Tom Lane committed
234
           The time zone 8 hours west from UTC (equivalent
235 236 237 238 239
           to PST).
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
240 241
         <term><literal>LOCAL</literal></term>
         <term><literal>DEFAULT</literal></term>
242 243
         <listitem>
          <para>
244 245 246
           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
247
           the server's operating system defaults to).
248 249 250 251
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
252

253 254
       See <xref linkend="datatype-timezones"> for more information
       about time zones.
255 256 257 258 259
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
260 261
 </refsect1>

262 263 264 265
 <refsect1>
  <title>Notes</title>

  <para>
266
   The function <function>set_config</function> provides equivalent
267 268 269 270
   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</>.
271 272
  </para>
 </refsect1>
273
 
274 275
 <refsect1>
  <title>Examples</title>
276

277
  <para>
278 279 280 281
   Set the schema search path:
<programlisting>
SET search_path TO my_schema, public;
</programlisting>
282
  </para>
283

284
  <para>
285
   Set the style of date to traditional
286 287
   <productname>POSTGRES</productname> with <quote>day before month</>
   input convention:
288
<screen>
289
SET datestyle TO postgres, dmy;
290
</screen>
291
  </para>
292

293
  <para>
294
   Set the time zone for Berkeley, California:
295 296
<screen>
SET TIME ZONE 'PST8PDT';
297 298
</screen>
  </para>
299

300 301 302 303
  <para>
   Set the time zone for Italy:
<screen>
SET TIME ZONE 'Europe/Rome';
304
</screen>
305
  </para>
306
 </refsect1>
307

308
 <refsect1>
309
  <title>Compatibility</title>
310

311 312 313 314 315 316 317
  <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>
318
 </refsect1>
319 320 321 322

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

323 324 325 326
  <simplelist type="inline">
   <member><xref linkend="SQL-RESET" endterm="SQL-RESET-title"></member>
   <member><xref linkend="SQL-SHOW" endterm="SQL-SHOW-title"></member>
  </simplelist>
327
 </refsect1>
328
</refentry>