set.sgml 8.3 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ref/set.sgml,v 1.90 2006/09/22 16:20:00 tgl 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 43 44 45 46 47 48 49 50 51 52
  <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>
53

54 55 56 57 58 59 60 61
  <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>
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 89 90 91 92 93
 </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>
94
    <term><replaceable class="PARAMETER">configuration_parameter</replaceable></term>
95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114
    <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>
115

116
  <para>
117 118 119 120
   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:
121 122

   <variablelist>
123
    <varlistentry>
124
     <term><literal>NAMES</literal></term>
125 126
     <listitem>
      <para>
Tom Lane's avatar
Tom Lane committed
127
       <literal>SET NAMES <replaceable>value</></> is an alias for
128
       <literal>SET client_encoding TO <replaceable>value</></>.
129 130 131 132 133
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
134
     <term><literal>SEED</literal></term>
135
     <listitem>
136
      <para>
137 138 139 140
       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.
141
      </para>
142

143
      <para>
144 145 146
       The seed can also be set by invoking the function
       <function>setseed</function>:
<programlisting>
147
SELECT setseed(<replaceable>value</replaceable>);
148
</programlisting>
149 150 151
      </para>
     </listitem>
    </varlistentry>
152

153
    <varlistentry>
154
     <term><literal>TIME ZONE</literal></term>
155
     <listitem>
156
      <para>
Tom Lane's avatar
Tom Lane committed
157
       <literal>SET TIME ZONE <replaceable>value</></> is an alias
158
       for <literal>SET timezone TO <replaceable>value</></>.  The
Tom Lane's avatar
Tom Lane committed
159
       syntax <literal>SET TIME ZONE</literal> allows special syntax
160
       for the time zone specification.  Here are examples of valid
161
       values:
162 163

       <variablelist>
164
        <varlistentry>
165
         <term><literal>'PST8PDT'</literal></term>
166 167
         <listitem>
          <para>
168
           The time zone for Berkeley, California.
169 170 171 172
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
173
         <term><literal>'Europe/Rome'</literal></term>
174 175
         <listitem>
          <para>
176
           The time zone for Italy.
177 178 179 180
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
181
         <term><literal>-7</literal></term>
182 183
         <listitem>
          <para>
Tom Lane's avatar
Tom Lane committed
184
           The time zone 7 hours west from UTC (equivalent
185
           to PDT).  Positive values are east from UTC.
186 187 188 189
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
190
         <term><literal>INTERVAL '-08:00' HOUR TO MINUTE</literal></term>
191 192
         <listitem>
          <para>
Tom Lane's avatar
Tom Lane committed
193
           The time zone 8 hours west from UTC (equivalent
194 195 196 197 198
           to PST).
          </para>
         </listitem>
        </varlistentry>
        <varlistentry>
199 200
         <term><literal>LOCAL</literal></term>
         <term><literal>DEFAULT</literal></term>
201 202 203
         <listitem>
          <para>
           Set the time zone to your local time zone (the one that
204
           the server's operating system defaults to).
205 206 207 208
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
209

210 211
       See <xref linkend="datatype-timezones"> for more information
       about time zones.
212 213 214 215 216
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
217 218
 </refsect1>

219 220 221 222
 <refsect1>
  <title>Notes</title>

  <para>
223
   The function <function>set_config</function> provides equivalent
224
   functionality. See <xref linkend="functions-admin">.
225 226
  </para>
 </refsect1>
227
 
228 229
 <refsect1>
  <title>Examples</title>
230

231
  <para>
232 233 234 235
   Set the schema search path:
<programlisting>
SET search_path TO my_schema, public;
</programlisting>
236
  </para>
237

238
  <para>
239
   Set the style of date to traditional
240 241
   <productname>POSTGRES</productname> with <quote>day before month</>
   input convention:
242
<screen>
243
SET datestyle TO postgres, dmy;
244
</screen>
245
  </para>
246

247
  <para>
248
   Set the time zone for Berkeley, California:
249 250
<screen>
SET TIME ZONE 'PST8PDT';
251 252
</screen>
  </para>
253

254 255 256 257
  <para>
   Set the time zone for Italy:
<screen>
SET TIME ZONE 'Europe/Rome';
258
</screen>
259
  </para>
260
 </refsect1>
261

262
 <refsect1>
263
  <title>Compatibility</title>
264

265 266 267 268 269 270 271
  <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>
272
 </refsect1>
273 274 275 276

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

277 278 279 280
  <simplelist type="inline">
   <member><xref linkend="SQL-RESET" endterm="SQL-RESET-title"></member>
   <member><xref linkend="SQL-SHOW" endterm="SQL-SHOW-title"></member>
  </simplelist>
281
 </refsect1>
282
</refentry>