set.sgml

关系型数据库 Postgresql 6.5.2

<refentry id="SQL-SET">
 <refmeta>
  <refentrytitle>
   SET
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname id="SQL-SET-TITLE">
   SET
  </refname>
  <refpurpose>
   Set run-time parameters for session
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1998-09-24</date>
  </refsynopsisdivinfo>
  <synopsis>
SET <replaceable class="PARAMETER">variable</replaceable> { TO | = } { '<replaceable class="PARAMETER">value</replaceable>' | DEFAULT }
SET TIME ZONE { '<replaceable class="PARAMETER">timezone</replaceable>' | LOCAL | DEFAULT }
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZED }
  </synopsis>

  <refsect2 id="R2-SQL-SET-1">
   <refsect2info>
    <date>1998-09-24</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>
		
    <variablelist>
     <varlistentry>
      <term>
<replaceable class="PARAMETER">variable</replaceable>
      </term>
      <listitem>
       <para>
	Settable global parameter.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>
<replaceable class="PARAMETER">value</replaceable>
      </term>
      <listitem>
       <para>
	New value of parameter.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    The possible variables and allowed values are:
		
    <variablelist>
     <varlistentry>
      <term>
CLIENT_ENCODING | NAMES
      </term>
      <listitem>
       <para>
	Sets the multi-byte client encoding. Parameters are:
	
	<variablelist>
	 <varlistentry>
	  <term>
<replaceable class="parameter">value</replaceable>
	  </term>
	  <listitem>
	   <para>
	    Sets the multi-byte client encoding to
	    <replaceable class="parameter">value</replaceable>.
	    The specified encoding must be supported by the backend.
	   </para>
	  </listitem>
	 </varlistentry>
	 
	 <varlistentry>
	  <term>
DEFAULT
	  </term>
	  <listitem>
	   <para>
	    Sets the multi-byte client encoding to the default value.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>

       <para>
	This is only enabled if multi-byte was specified to configure.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
DateStyle
      </term>
      <listitem>
       <para>
	Set the date/time representation style. Affects the output format,
	and in some cases it can affect the interpretation of input.

	<variablelist>
	 <varlistentry>
	  <term>
ISO
	  </term>
	  <listitem>
	   <para>
	    use ISO 8601-style dates and times
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
SQL
	  </term>
	  <listitem>
	   <para>
	    use Oracle/Ingres-style dates and times
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
Postgres
	  </term>
	  <listitem>
	   <para>
	    use traditional <productname>Postgres</productname> format
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
European
	  </term>
	  <listitem>
	   <para>
	    use dd/mm/yyyy for numeric date representations.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
NonEuropean
	  </term>
	  <listitem>
	   <para>
	    use mm/dd/yyyy for numeric date representations.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
German
	  </term>
	  <listitem>
	   <para>
	    use dd.mm.yyyy for numeric date representations.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
US
	  </term>
	  <listitem>
	   <para>
	    same as 'NonEuropean'
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
default
	  </term>
	  <listitem>
	   <para>
	    restores the default values ('US,Postgres')
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>

       <para>
	Date format initialization may be done by:
	<simplelist>
	 <member>
	  Setting the <envar>PGDATESTYLE</envar> environment variable.
	 </member>
	 <member>
	  Running postmaster using the <option>-o -e</option> option to set
	  dates to the <literal>European</literal> convention.
	  Note that this affects only the some combinations of date styles; for example
	  the ISO style is not affected by this parameter.
	 </member>
	 <member>
	  Changing variables in 
	  <filename>src/backend/utils/init/globals.c</filename>.
	 </member>
	</simplelist>
       </para>
       <para>
	The variables in <filename>globals.c</filename> which can be changed are:
	<simplelist>
	 <member>
	  bool EuroDates = false | true
	 </member>
	 <member>
	  int  DateStyle = USE_ISO_DATES | USE_POSTGRES_DATES | USE_SQL_DATES | USE_GERMAN_DATES
	 </member>
	</simplelist>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
SERVER_ENCODING
      </term>
      <listitem>
       <para>
	Sets the multi-byte server encoding
	
	<variablelist>
	 <varlistentry>
	  <term>
<replaceable class="parameter">value</replaceable>
	  </term>
	  <listitem>
	   <para>
	    Sets the multi-byte server encoding.
	   </para>
	  </listitem>
	 </varlistentry>
	 
	 <varlistentry>
	  <term>
DEFAULT
	  </term>
	  <listitem>
	   <para>
	    Sets the multi-byte server encoding.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
       <para>
	This is only enabled if multi-byte was specified to configure.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
TIMEZONE
      </term>
      <listitem>
       <para>
	The possible values for timezone depends on your operating
	system. For example on Linux /usr/lib/zoneinfo contains the
	database of timezones.
       </para>
       <para>
	Here are some valid values for timezone:
				
	<variablelist>
	 <varlistentry>
	  <term>
'PST8PDT'
	  </term>
	  <listitem>
	   <para>
	    set the timezone for California
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
'Portugal'
	  </term>
	  <listitem>
	   <para>
	    set time zone for Portugal.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
'Europe/Rome'
	  </term>
	  <listitem>
	   <para>
	    set time zone for Italy.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>
DEFAULT
	  </term>
	  <listitem>
	   <para>
	    set time zone to your local timezone
	    (value of the TZ environment variable).
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
       <para>
	If an invalid time zone is specified, the time zone
	becomes GMT (on most systems anyway).
       </para>
       <para>
	A frontend which uses libpq may be initialized by setting the PGTZ
	environment variable.
       </para>
       <para>
	The second syntax shown above, allows one to set the timezone
	with a syntax similar to SQL92 <command>SET TIME ZONE</command>.
	The LOCAL keyword is just an alternate form
	of DEFAULT for SQL92 compatibility.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
TRANSACTION ISOLATION LEVEL
      </term>
      <listitem>
       <para>
	Sets the isolation level for the current transaction.
	
	<variablelist>
	 <varlistentry>
	  <term>
READ COMMITTED
	  </term>
	  <listitem>
	   <para>
	    The current transaction queries read only rows committed
	    before a query began. READ COMMITTED is the default.
	   </para>

	   <note>
	   <para>
	    <acronym>SQL92</acronym> standard requires 
	    SERIALIZABLE to be the default isolation level.
	   </para>
	   </note>
	  </listitem>
	 </varlistentry>
	 
	 <varlistentry>
	  <term>
SERIALIZABLE
	  </term>
	  <listitem>
	   <para>
	    The current transaction queries read only rows committed
	    before first DML statement 
	    (<command>SELECT/INSERT/DELETE/UPDATE/FETCH/COPY_TO</command>)
	    was executed in this transaction. 
	   </para>
	  </listitem>
	 </varlistentry>
	 
	</variablelist>
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    There are also several internal or optimization
    parameters which can be specified
    by the <command>SET</command> command: