File: sql-set.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (821 lines) | stat: -rw-r--r-- 12,169 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>SET</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="SELECT INTO
  "
HREF="sql-selectinto.html"><LINK
REL="NEXT"
TITLE="SHOW"
HREF="sql-show.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-selectinto.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-show.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>SET</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>SET &#8212;    Set run-time parameters for session</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
></I
></TT
>
SET <TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
> { TO | = } { '<TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
>' | DEFAULT }
SET TIME ZONE { '<TT
CLASS="REPLACEABLE"
><I
>timezone</I
></TT
>' | LOCAL };</PRE
><DIV
CLASS="REFSECT2"
><H3
>Inputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
></DT
><DD
><P
>Settable global parameter.&#13;</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
></DT
><DD
><P
>New value of parameter.</P
></DD
></DL
><P>&#13;</P
><P
>The possible variables and allowed values are:

<P
></P
></P><DL
><DT
>DateStyle</DT
><DD
><P
>&#13;<P
></P
></P><DL
><DT
>ISO</DT
><DD
><P
>use ISO 8601-style dates and times</P
></DD
><DT
>SQL</DT
><DD
><P
>use Oracle/Ingres-style dates and times</P
></DD
><DT
>Postgres</DT
><DD
><P
>use traditional <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> format</P
></DD
><DT
>European</DT
><DD
><P
>use dd/mm/yyyy for numeric date representations.</P
></DD
><DT
>NonEuropean</DT
><DD
><P
>use mm/dd/yyyy for numeric date representations.</P
></DD
><DT
>German</DT
><DD
><P
>use dd.mm.yyyy for numeric date representations.</P
></DD
><DT
>US</DT
><DD
><P
>same as 'NonEuropean'</P
></DD
><DT
>default</DT
><DD
><P
>restores the default values ('US,Postgres')</P
></DD
></DL
><P>&#13;</P
><P
>                Date format initialization my be done by:
<P
></P
><TABLE
BORDER="0"
><TR
><TD
>Setting PGDATESTYLE environment variable.&#13;</TD
></TR
><TR
><TD
>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.</TD
></TR
><TR
><TD
>Changing variables in 
<TT
CLASS="FILENAME"
>src/backend/utils/init/globals.c</TT
>.</TD
></TR
></TABLE
><P
></P
>&#13;</P
><P
>The variables in <TT
CLASS="FILENAME"
>globals.c</TT
> which can be changed are:
<PRE
CLASS="PROGRAMLISTING"
>bool EuroDates = false
                 true
int  DateStyle = USE_ISO_DATES
                 USE_POSTGRES_DATES
                 USE_ISO_DATES
                 USE_SQL_DATES
                 USE_GERMAN_DATES</PRE
>&#13;</P
></DD
><DT
>TIMEZONE</DT
><DD
><P
>                The possible values for timezone depends on your operating
                system. For example on Linux /usr/lib/zoneinfo contains the
                database of timezones.</P
><P
>                Here are some valid values for timezone:

<P
></P
></P><DL
><DT
>'PST8PDT'</DT
><DD
><P
>set the timezone for California</P
></DD
><DT
>'Portugal'</DT
><DD
><P
>set time zone for Portugal.</P
></DD
><DT
>'Europe/Rome'</DT
><DD
><P
>set time zone for Italy.</P
></DD
><DT
>DEFAULT</DT
><DD
><P
>set time zone to your local timezone
(value of the TZ environment variable).</P
></DD
></DL
><P>&#13;</P
><P
>If an invalid time zone is specified, the time zone
becomes GMT (on most systems anyway).&#13;</P
><P
>A frontend which uses libpq may be initialized by setting the PGTZ
environment variable.&#13;</P
><P
>The second syntax shown above, allows one to set the timezone
with a syntax similar to SQL92 <B
CLASS="COMMAND"
>SET TIME ZONE</B
>.
The LOCAL keyword is just an alternate form
of DEFAULT for SQL92 compatibility.&#13;</P
></DD
></DL
><P>

There are also several internal or optimization
 parameters which can be specified
by the <B
CLASS="COMMAND"
>SET</B
> command:

<P
></P
></P><DL
><DT
>COST_HEAP</DT
><DD
><P
>Sets the default cost of a heap scan for use by the optimizer.

<P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>float4</I
></TT
></DT
><DD
><P
>Set the cost of a heap scan to the specified floating point value.&#13;</P
></DD
><DT
>DEFAULT</DT
><DD
><P
>Sets the cost of a heap scan to the default value.</P
></DD
></DL
><P>&#13;</P
><P
>                The frontend may be initialized by setting the PGCOSTHEAP
                environment variable.&#13;</P
></DD
><DT
>COST_INDEX</DT
><DD
><P
>Sets the default cost of an index scan for use by the optimizer.

<P
></P
></P><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>float4</I
></TT
></DT
><DD
><P
>Set the cost of an index scan to the specified floating point value.&#13;</P
></DD
><DT
>DEFAULT</DT
><DD
><P
>Sets the cost of an index scan to the default value.</P
></DD
></DL
><P>&#13;</P
><P
>                The frontend may be initialized by setting the PGCOSTINDEX
                environment variable.&#13;</P
></DD
><DT
>GEQO</DT
><DD
><P
>Sets the threshold for using the genetic optimizer algorithm.

<P
></P
></P><DL
><DT
>On</DT
><DD
><P
>enables the genetic optimizer algorithm
                           for statements with 8 or more tables.</P
></DD
><DT
>On=<TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
></DT
><DD
><P
>Takes an integer argument to enable the genetic optimizer algorithm
 for statements with <TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
>
 or more tables in the query.</P
></DD
><DT
>Off</DT
><DD
><P
>disables the genetic optimizer algorithm.</P
></DD
><DT
>DEFAULT</DT
><DD
><P
>Equivalent to specifying <B
CLASS="COMMAND"
>SET GEQO='on'</B
></P
></DD
></DL
><P>&#13;</P
><P
>                This algorithm is on by default, which used GEQO for
                statements of eight or more tables. 
                (See the chapter on GEQO in the Programmer's Guide
for more information).&#13;</P
><P
>                The frontend may be initialized by setting PGGEQO
                environment variable.</P
></DD
><DT
>R_PLANS</DT
><DD
><P
>Determines whether right-hand plan evaluation is allowed:

<P
></P
></P><DL
><DT
>On</DT
><DD
><P
>enables right-hand evaluation of plans.&#13;</P
></DD
><DT
>Off</DT
><DD
><P
>disables right-hand evaluation of plans.&#13;</P
></DD
><DT
>DEFAULT</DT
><DD
><P
>Equivalent to specifying <B
CLASS="COMMAND"
>SET R_PLANS='off'</B
>.</P
></DD
></DL
><P>&#13;</P
><P
>                It may be useful when joining big relations with
                small ones. This algorithm is off by default.
                It's not used by GEQO anyway.</P
><P
>                The frontend may be initialized by setting the PGRPLANS
                environment variable.</P
></DD
><DT
>KSQO</DT
><DD
><P
><I
CLASS="FIRSTTERM"
>Key Set Query Optimizer</I
> forces the query optimizer
to optimize repetative OR clauses such as generated by
<SPAN
CLASS="PRODUCTNAME"
>MicroSoft Access</SPAN
>:

<P
></P
></P><DL
><DT
>On</DT
><DD
><P
>enables this optimization.&#13;</P
></DD
><DT
>Off</DT
><DD
><P
>disables this optimization.&#13;</P
></DD
><DT
>DEFAULT</DT
><DD
><P
>Equivalent to specifying <B
CLASS="COMMAND"
>SET KSQO='off'</B
>.</P
></DD
></DL
><P>&#13;</P
><P
>                It may be useful when joining big relations with
                small ones. This algorithm is off by default.
                It's not used by GEQO anyway.</P
><P
>                The frontend may be initialized by setting the PGRPLANS
                environment variable.&#13;</P
></DD
><DT
>QUERY_LIMIT</DT
><DD
><P
>Sets the number of rows returned by a query.

<P
></P
></P><DL
><DT
>Value</DT
><DD
><P
>Maximum number of rows to return for a query.  The default is to allow
an unlimited number of rows.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
></DT
><DD
><P
>Sets the maximum number of rows returned by a
query to <TT
CLASS="REPLACEABLE"
><I
>#</I
></TT
>.</P
></DD
><DT
>DEFAULT</DT
><DD
><P
>Sets the maximum number of rows returned by a query to be unlimited.</P
><P
>By default, there is no limit to the number of rows
returned by a query.</P
></DD
></DL
><P>&#13;</P
></DD
></DL
><P></P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Outputs</H3
><P
>&#13;<P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>SET VARIABLE</SPAN
></DT
><DD
><P
>          Message returned if successfully.&#13;</P
></DD
><DT
><SPAN
CLASS="RETURNVALUE"
>WARN:  Bad value for <TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
> (<TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
>)</SPAN
></DT
><DD
><P
>          If the command fails to set variable.&#13;</P
></DD
></DL
><P>&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Description</H2
><P
><B
CLASS="COMMAND"
>SET</B
> will modify configuration parameters for variable during
   a session.&#13;</P
><P
>   Current values can be obtained using <B
CLASS="COMMAND"
>SHOW</B
>, and values
   can be restored to the defaults using <B
CLASS="COMMAND"
>RESET</B
>.
   Parameters and values are case-insensitive. Note that the value
   field is always specified as a string, so is enclosed in
   single-quotes.</P
><P
>   <B
CLASS="COMMAND"
>SET TIME ZONE</B
> changes the session's
 default time zone offset.
   A SQL-session always begins with an initial default time zone
   offset.
   The <B
CLASS="COMMAND"
>SET TIME ZONE</B
> statement is used to change the default
   time zone offset for the current SQL session.&#13;</P
><DIV
CLASS="REFSECT2"
><H3
>Notes</H3
><P
>The <B
CLASS="COMMAND"
>SET <TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
></B
>
 statement is a <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> language extension.&#13;</P
><P
>   Refer to <B
CLASS="COMMAND"
>SHOW</B
> and <B
CLASS="COMMAND"
>RESET</B
> to 
display or reset the current values.&#13;</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Usage</H2
><P
></P
><PRE
CLASS="PROGRAMLISTING"
>--Set the style of date to ISO:
--
SET DATESTYLE TO 'ISO';</PRE
><PRE
CLASS="PROGRAMLISTING"
> 
--Set GEQO to default:
--
SET GEQO = DEFAULT;</PRE
><PRE
CLASS="PROGRAMLISTING"
> 
--Turn on right-hand evaluation of plans:
--
SET R_PLANS TO 'on';</PRE
><PRE
CLASS="PROGRAMLISTING"
> 
--set the timezone for Berkeley, California:
SET TIME ZONE 'PST8PDT';

SELECT CURRENT_TIMESTAMP AS today;

   today
   ----------------------
   1998-03-31 07:41:21-08</PRE
><PRE
CLASS="PROGRAMLISTING"
> 
--set the timezone for Italy:
SET TIME ZONE 'Europe/Rome';

SELECT CURRENT_TIMESTAMP AS today;

   today
   ----------------------
   1998-03-31 17:41:31+02</PRE
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Compatibility</H2
><P
></P
><DIV
CLASS="REFSECT2"
><H3
>SQL92</H3
><P
>   There is no
<B
CLASS="COMMAND"
>SET <TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
></B
>
   in <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
>.

   The <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> syntax for <B
CLASS="COMMAND"
>SET TIME ZONE</B
>
 is slightly different,
 allowing only a single integer value for time zone specification:

<PRE
CLASS="PROGRAMLISTING"
>SET TIME ZONE { interval_value_expression | LOCAL }</PRE
>&#13;</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-selectinto.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-show.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>SELECT INTO</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>SHOW</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>