<REFENTRY ID="SQL-SET">
<REFMETA>
<REFENTRYTITLE>
SET
</REFENTRYTITLE>
<REFMISCINFO>SQL - Language Statements</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>
SET
</REFNAME>
<REFPURPOSE>
   Set run-time parameters for session
</REFPURPOSE>
  </refnamediv>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1998-09-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
<REPLACEABLE CLASS="PARAMETER">
</REPLACEABLE>
SET <REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> { TO | = } { '<REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>' | DEFAULT }
SET TIME ZONE { '<REPLACEABLE CLASS="PARAMETER">timezone</REPLACEABLE>' | LOCAL };
</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>
			  DateStyle
			</TERM>
			<LISTITEM>
			  <PARA>
				
				<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>
			</listitem>
		  </varlistentry>
		</variablelist>
	  </para>
	  <para>
		Date format initialization my be done by:
		<simplelist>
		  <member>
			Setting PGDATESTYLE environment variable.
		  </member>
		  <member>
			Running postmaster using -oe parameter to set
			dates to the 'European' 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:
		<programlisting>
		  bool EuroDates = false
		  true
		  int  DateStyle = USE_ISO_DATES
		  USE_POSTGRES_DATES
		  USE_ISO_DATES
		  USE_SQL_DATES
		  USE_GERMAN_DATES
		</programlisting>
	  </para>
	  <para>
		<variablelist>
		  <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>
		</variablelist>
	  </para>
	  <para>
		There are also several internal or optimization
		parameters which can be specified
		by the <command>SET</command> command:
		
		<variablelist>
		  <varlistentry>
			<term>
			  COST_HEAP
			</term>
			<listitem>
			  <para>
				Sets the default cost of a heap scan for use by the optimizer.
				
				<variablelist>
				  <varlistentry>
					<term>
					  <replaceable class="parameter">float4</replaceable>
					</term>
					<listitem>
					  <para>
						Set the cost of a heap scan to the specified floating point value.
					  </para>
					</listitem>
				  </varlistentry>
				  
				  <varlistentry>
					<term>
					  DEFAULT
					</term>
					<listitem>
					  <para>
						Sets the cost of a heap scan to the default value.
					  </para>
					</listitem>
				  </varlistentry>
				</variablelist>
			  </para>
			  <para>
				The frontend may be initialized by setting the PGCOSTHEAP
				environment variable.
			  </para>
			  <variablelist>
				<varlistentry>
				  <term>
					COST_INDEX
				  </term>
				  <listitem>
					<para>
					  Sets the default cost of an index scan for use by the optimizer.
					</para>
				  </listitem>
				</varlistentry>
				
				<varlistentry>
				  <term>
					<replaceable class="parameter">float4</replaceable>
				  </term>
				  <listitem>
					<para>
					  Set the cost of an index scan to the specified floating point value.
					</para>
				  </listitem>
				</varlistentry>
				
				<varlistentry>
				  <term>
					DEFAULT
				  </term>
				  <listitem>
					<para>
					  Sets the cost of an index scan to the default value.
					</para>
				  </listitem>
				</varlistentry>
			  </variablelist>
			</listitem>
		  </varlistentry>
		</variablelist>
	  </para>
	  <para>
		The frontend may be initialized by setting the PGCOSTINDEX
		environment variable.
		
		<variablelist>
		  <varlistentry>
			<term>
			  GEQO
			</term>
			<listitem>
			  <para>
				Sets the threshold for using the genetic optimizer algorithm.
			  </para>
			  
			  <variablelist>
				<varlistentry>
				  <term>
					On
				  </term>
				  <listitem>
					<para>
					  enables the genetic optimizer algorithm
					  for statements with 6 or more tables.
					</para>
				  </listitem>
				</varlistentry>
				<varlistentry>
				  <term>
					On=<replaceable class="parameter">#</replaceable>
				  </term>
				  <listitem>
					<para>
					  Takes an integer argument to enable the genetic optimizer algorithm
					  for statements with <replaceable class="parameter">#</replaceable>
					  or more tables in the query.
					</para>
				  </listitem>
				</varlistentry>
				<varlistentry>
				  <term>
					Off
				  </term>
				  <listitem>
					<para>
					  disables the genetic optimizer algorithm.
					</para>
				  </listitem>
				</varlistentry>
				<varlistentry>
				  <term>
					DEFAULT
				  </term>
				  <listitem>
					<para>
					  Equivalent to specifying <command>SET GEQO='on'</command>
					</para>
				  </listitem>
				</varlistentry>
			  </variablelist>
			</listitem>
		  </varlistentry>
		</variablelist>
	  </para>
	  <para>
		This algorithm is on by default, which used GEQO for
		statements of six or more tables.
		(See the chapter on GEQO in the Programmer's Guide
		for more information).
	  </para>
	  <para>
		The frontend may be initialized by setting PGGEQO
		environment variable.
	  </para>
	  <para>
		It may be useful when joining big relations with
		small ones. This algorithm is off by default.
		It's not used by GEQO anyway.

		<variablelist>
		  <varlistentry>
			<term>
			  KSQO
			</term>
			<listitem>
			  <para>
				<firstterm>Key Set Query Optimizer</firstterm> forces the query optimizer
				to optimize repetative OR clauses such as generated by
				<productname>MicroSoft Access</productname>:
			  </para>
			  
			  <variablelist>
				<varlistentry>
				  <term>
					On
				  </term>
				  <listitem>
					<para>
					  enables this optimization.
					</para>
				  </listitem>
				</varlistentry>
				
				<varlistentry>
				  <term>
					Off
				  </term>
				  <listitem>
					<para>
					  disables this optimization.
					</para>
				  </listitem>
				</varlistentry>
				
				<varlistentry>
				  <term>
					DEFAULT
				  </term>
				  <listitem>
					<para>
					  Equivalent to specifying <command>SET KSQO='off'</command>.
					</para>
				  </listitem>
				</varlistentry>
			  </variablelist>
			</listitem>
		  </varlistentry>
		</variablelist>
	  </para>
	  <para>
		It may be useful when joining big relations with
		small ones. This algorithm is off by default.
		It's not used by GEQO anyway.
	  </para>
	  <para>
		The frontend may be initialized by setting the PGKSQO
		environment variable.
		<variablelist>
		  <varlistentry>
			<term>
			  QUERY_LIMIT
			</term>
			<listitem>
			  <para>
				Sets the number of rows returned by a query.
			  </para>
			  
			  <variablelist>
				<varlistentry>
				  <term>
					Value
				  </term>
				  <listitem>
					<para>
					  Maximum number of rows to return for a query.  The default is to allow
					  an unlimited number of rows.
					</para>
				  </listitem>
				</varlistentry>
				<varlistentry>
				  <term>
					<replaceable class="parameter">#</replaceable>
				  </term>
				  <listitem>
					<para>
					  Sets the maximum number of rows returned by a
					  query to <replaceable class="parameter">#</replaceable>.
					</para>
				  </listitem>
				</varlistentry>
				<varlistentry>
				  <term>
					DEFAULT
				  </term>
				  <listitem>
					<para>
					  Sets the maximum number of rows returned by a query to be unlimited.
					</para>
				  </listitem>
				</varlistentry>
			  </variablelist>
			  <para>
				By default, there is no limit to the number of rows
				returned by a query.
			  </para>
			</listitem>
		  </varlistentry>
		</variablelist>
	  </para>
	</REFSECT2>
	
	<REFSECT2 ID="R2-SQL-SET-2">
	  <REFSECT2INFO>
		<DATE>1998-09-24</DATE>
	  </REFSECT2INFO>
	  <TITLE>
		Outputs
	  </TITLE>
	  <PARA>
		
		<VARIABLELIST>
		  <VARLISTENTRY>
			<TERM>
			  <returnvalue>SET VARIABLE</returnvalue>
			</TERM>
			<LISTITEM>
			  <PARA>
				Message returned if successfully.
			  </para>
			</listitem>
		  </varlistentry>
		  
		  <VARLISTENTRY>
			<TERM>
			  <returnvalue>WARN:  Bad value for <replaceable class="parameter">variable</replaceable> (<replaceable class="parameter">value</replaceable>)</returnvalue>
			</TERM>
			<LISTITEM>
			  <PARA>
				If the command fails to set variable.
			  </para>
			</listitem>
		  </varlistentry>
		  
		</VARIABLELIST>
	  </para>
	</REFSECT2>
  </REFSYNOPSISDIV>
  
  <REFSECT1 ID="R1-SQL-SET-1">
	<REFSECT1INFO>
	  <DATE>1998-09-24</DATE>
	</REFSECT1INFO>
	<TITLE>
	  Description
	</TITLE>
	<PARA>
	  <command>SET</command> will modify configuration parameters for variable during
   a session.
	</para>
	<para>
	  Current values can be obtained using <command>SHOW</command>, and values
	  can be restored to the defaults using <command>RESET</command>.
	  Parameters and values are case-insensitive. Note that the value
	  field is always specified as a string, so is enclosed in
	  single-quotes.
	</para>
	<para>
	  <command>SET TIME ZONE</command> changes the session's
	  default time zone offset.
	  A SQL-session always begins with an initial default time zone
	  offset.
	  The <command>SET TIME ZONE</command> statement is used to change the default
	  time zone offset for the current SQL session.
	</para>

	<REFSECT2 ID="R2-SQL-SET-3">
	  <REFSECT2INFO>
		<DATE>1998-09-24</DATE>
	  </REFSECT2INFO>
	  <TITLE>
		Notes
	  </TITLE>
	  <PARA>
		The <command>SET <replaceable class="parameter">variable</replaceable></command>
		statement is a <productname>Postgres</productname> language extension.
	  </para>
	  <para>
		Refer to <command>SHOW</command> and <command>RESET</command> to 
		display or reset the current values.
	  </para>
	</REFSECT2>
  </REFSECT1>

  <REFSECT1 ID="R1-SQL-SET-2">
	<TITLE>
	  Usage
	</TITLE>
	<PARA>
	</PARA>
	<ProgramListing>
	  --Set the style of date to ISO:
	  --
	  SET DATESTYLE TO 'ISO';
	</programlisting>
	<programlisting> 
	  --Enable GEQO for queries with 4 or more tables
	  --
	  SET GEQO ON=4;
	</programlisting>
	<programlisting> 
	  --Set GEQO to default:
	  --
	  SET GEQO = DEFAULT;
	</programlisting>
	<programlisting> 
	  --set the timezone for Berkeley, California:
	  SET TIME ZONE 'PST8PDT';
	  
	  SELECT CURRENT_TIMESTAMP AS today;
	  
	  today
	  ----------------------
	  1998-03-31 07:41:21-08
	</programlisting>
	<programlisting> 
	  --set the timezone for Italy:
	  SET TIME ZONE 'Europe/Rome';
	  
	  SELECT CURRENT_TIMESTAMP AS today;
	  
	  today
	  ----------------------
	  1998-03-31 17:41:31+02
	</ProgramListing>

  </REFSECT1>

  <REFSECT1 ID="R1-SQL-SET-3">
	<TITLE>
	  Compatibility
	</TITLE>
	<PARA>
	</PARA>
	
	<REFSECT2 ID="R2-SQL-SET-4">
	  <REFSECT2INFO>
		<DATE>1998-09-24</DATE>
	  </REFSECT2INFO>
	  <TITLE>
		SQL92
	  </TITLE>
	  <PARA>
		There is no
		<command>SET <replaceable class="parameter">variable</replaceable></command>
		in <acronym>SQL92</acronym>.
		
		The <acronym>SQL92</acronym> syntax for <command>SET TIME ZONE</command>
		is slightly different,
		allowing only a single integer value for time zone specification:
		
		<programlisting>
		  SET TIME ZONE { interval_value_expression | LOCAL }
		</programlisting>
	  </para>
	</refsect2>
  </refsect1>
</REFENTRY>
