func.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

<!--
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.291.2.5 2005/12/21 23:23:09 momjian Exp $
PostgreSQL documentation
-->

 <chapter id="functions">
  <title>Functions and Operators</title>

  <indexterm zone="functions">
   <primary>function</primary>
  </indexterm>

  <indexterm zone="functions">
   <primary>operator</primary>
  </indexterm>

  <para>
   <productname>PostgreSQL</productname> provides a large number of
   functions and operators for the built-in data types.  Users can also
   define their own functions and operators, as described in
   <xref linkend="server-programming">.  The
   <application>psql</application> commands <command>\df</command> and
   <command>\do</command> can be used to show the list of all actually
   available functions and operators, respectively.
  </para>

  <para>
   If you are concerned about portability then take note that most of
   the functions and operators described in this chapter, with the
   exception of the most trivial arithmetic and comparison operators
   and some explicitly marked functions, are not specified by the
   <acronym>SQL</acronym> standard. Some of the extended functionality
   is present in other <acronym>SQL</acronym> database management
   systems, and in many cases this functionality is compatible and
   consistent between the various implementations.  This chapter is also
   not exhaustive;  additional functions appear in relevant sections of 
   the manual.
  </para>


  <sect1 id="functions-logical">
   <title>Logical Operators</title>

   <indexterm zone="functions-logical">
    <primary>operator</primary>
    <secondary>logical</secondary>
   </indexterm>

   <indexterm>
    <primary>Boolean</primary>
    <secondary>operators</secondary>
    <see>operators, logical</see>
   </indexterm>

   <para>
    The usual logical operators are available:

    <indexterm>
     <primary>AND (operator)</primary>
    </indexterm>

    <indexterm>
     <primary>OR (operator)</primary>
    </indexterm>

    <indexterm>
     <primary>NOT (operator)</primary>
    </indexterm>

    <indexterm>
     <primary>conjunction</primary>
    </indexterm>

    <indexterm>
     <primary>disjunction</primary>
    </indexterm>

    <indexterm>
     <primary>negation</primary>
    </indexterm>

    <simplelist>
     <member><literal>AND</></member>
     <member><literal>OR</></member>
     <member><literal>NOT</></member>
    </simplelist>

    <acronym>SQL</acronym> uses a three-valued Boolean logic where the null value represents
    <quote>unknown</quote>.  Observe the following truth tables:

    <informaltable>
     <tgroup cols="4">
      <thead>
       <row>
        <entry><replaceable>a</replaceable></entry>
        <entry><replaceable>b</replaceable></entry>
        <entry><replaceable>a</replaceable> AND <replaceable>b</replaceable></entry>
        <entry><replaceable>a</replaceable> OR <replaceable>b</replaceable></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>TRUE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>TRUE</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
        <entry>FALSE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>NULL</entry>
        <entry>FALSE</entry>
        <entry>NULL</entry>
       </row>

       <row>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
        <entry>NULL</entry>
       </row>
      </tbody>
     </tgroup>
    </informaltable>

    <informaltable>
     <tgroup cols="2">
      <thead>
       <row>
        <entry><replaceable>a</replaceable></entry>
        <entry>NOT <replaceable>a</replaceable></entry>
       </row>
      </thead>

      <tbody>
       <row>
        <entry>TRUE</entry>
        <entry>FALSE</entry>
       </row>

       <row>
        <entry>FALSE</entry>
        <entry>TRUE</entry>
       </row>

       <row>
        <entry>NULL</entry>
        <entry>NULL</entry>
       </row>
      </tbody>
     </tgroup>
    </informaltable>
   </para>

   <para>
    The operators <literal>AND</literal> and <literal>OR</literal> are
    commutative, that is, you can switch the left and right operand
    without affecting the result.  But see <xref
    linkend="syntax-express-eval"> for more information about the
    order of evaluation of subexpressions.
   </para>
  </sect1>

  <sect1 id="functions-comparison">
   <title>Comparison Operators</title>

   <indexterm zone="functions-comparison">
    <primary>comparison</primary>
    <secondary>operators</secondary>
   </indexterm>

   <para>
    The usual comparison operators are available, shown in <xref
    linkend="functions-comparison-table">.
   </para>

   <table id="functions-comparison-table">
    <title>Comparison Operators</title>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Operator</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry> <literal>&lt;</literal> </entry>
       <entry>less than</entry>
      </row>

      <row>
       <entry> <literal>&gt;</literal> </entry>
       <entry>greater than</entry>
      </row>

      <row>
       <entry> <literal>&lt;=</literal> </entry>
       <entry>less than or equal to</entry>
      </row>

      <row>
       <entry> <literal>&gt;=</literal> </entry>
       <entry>greater than or equal to</entry>
      </row>

      <row>
       <entry> <literal>=</literal> </entry>
       <entry>equal</entry>
      </row>

      <row>
       <entry> <literal>&lt;&gt;</literal> or <literal>!=</literal> </entry>
       <entry>not equal</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <note>
    <para>
     The <literal>!=</literal> operator is converted to
     <literal>&lt;&gt;</literal> in the parser stage.  It is not
     possible to implement <literal>!=</literal> and
     <literal>&lt;&gt;</literal> operators that do different things.
    </para>
   </note>

   <para>
    Comparison operators are available for all data types where this
    makes sense.  All comparison operators are binary operators that
    return values of type <type>boolean</type>; expressions like
    <literal>1 &lt; 2 &lt; 3</literal> are not valid (because there is
    no <literal>&lt;</literal> operator to compare a Boolean value with
    <literal>3</literal>).
   </para>

   <para>
    <indexterm>
     <primary>BETWEEN</primary>
    </indexterm>
    In addition to the comparison operators, the special
    <token>BETWEEN</token> construct is available.
<synopsis>
<replaceable>a</replaceable> BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
</synopsis>
    is equivalent to
<synopsis>
<replaceable>a</replaceable> &gt;= <replaceable>x</replaceable> AND <replaceable>a</replaceable> &lt;= <replaceable>y</replaceable>
</synopsis>
    Similarly,
<synopsis>
<replaceable>a</replaceable> NOT BETWEEN <replaceable>x</replaceable> AND <replaceable>y</replaceable>
</synopsis>
    is equivalent to
<synopsis>
<replaceable>a</replaceable> &lt; <replaceable>x</replaceable> OR <replaceable>a</replaceable> &gt; <replaceable>y</replaceable>
</synopsis>
    There is no difference between the two respective forms apart from
    the <acronym>CPU</acronym> cycles required to rewrite the first one
    into the second one internally.
    <indexterm>
     <primary>BETWEEN SYMETRIC</primary>
    </indexterm>
    <token>BETWEEN SYMMETRIC</> is the same as <literal>BETWEEN</>
    except there is no requirement that the argument to the left of <literal>AND</> be less than
    or equal to the argument on the right;  the proper range is automatically determined.
   </para>

   <para>
    <indexterm>
     <primary>IS NULL</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT NULL</primary>
    </indexterm>
    <indexterm>
     <primary>ISNULL</primary>
    </indexterm>
    <indexterm>
     <primary>NOTNULL</primary>
    </indexterm>
    To check whether a value is or is not null, use the constructs
<synopsis>
<replaceable>expression</replaceable> IS NULL
<replaceable>expression</replaceable> IS NOT NULL
</synopsis>
    or the equivalent, but nonstandard, constructs
<synopsis>
<replaceable>expression</replaceable> ISNULL
<replaceable>expression</replaceable> NOTNULL
</synopsis>
    <indexterm><primary>null value</primary><secondary>comparing</secondary></indexterm>
   </para>

   <para>
    Do <emphasis>not</emphasis> write
    <literal><replaceable>expression</replaceable> = NULL</literal>
    because <literal>NULL</> is not <quote>equal to</quote>
    <literal>NULL</>.  (The null value represents an unknown value,
    and it is not known whether two unknown values are equal.) This
    behavior conforms to the SQL standard.
   </para>

  <tip>
   <para>
    Some applications may expect that
    <literal><replaceable>expression</replaceable> = NULL</literal>
    returns true if <replaceable>expression</replaceable> evaluates to
    the null value.  It is highly recommended that these applications
    be modified to comply with the SQL standard. However, if that
    cannot be done the <xref linkend="guc-transform-null-equals">
    configuration variable is available. If it is enabled,
    <productname>PostgreSQL</productname> will convert <literal>x =
    NULL</literal> clauses to <literal>x IS NULL</literal>.  This was
    the default behavior in <productname>PostgreSQL</productname>
    releases 6.5 through 7.1.
   </para>
  </tip>

   <para>
    <indexterm>
     <primary>IS DISTINCT FROM</primary>
    </indexterm>
    The ordinary comparison operators yield null (signifying <quote>unknown</>)
    when either input is null.  Another way to do comparisons is with the
    <literal>IS DISTINCT FROM</literal> construct:
<synopsis>
<replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable>
</synopsis>
    For non-null inputs this is the same as the <literal>&lt;&gt;</> operator.
    However, when both inputs are null it will return false, and when just
    one input is null it will return true.  Thus it effectively acts as though
    null were a normal data value, rather than <quote>unknown</>.
   </para>

   <para>
    <indexterm>
     <primary>IS TRUE</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT TRUE</primary>
    </indexterm>
    <indexterm>
     <primary>IS FALSE</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT FALSE</primary>
    </indexterm>
    <indexterm>
     <primary>IS UNKNOWN</primary>
    </indexterm>
    <indexterm>
     <primary>IS NOT UNKNOWN</primary>
    </indexterm>
    Boolean values can also be tested using the constructs
<synopsis>
<replaceable>expression</replaceable> IS TRUE
<replaceable>expression</replaceable> IS NOT TRUE
<replaceable>expression</replaceable> IS FALSE
<replaceable>expression</replaceable> IS NOT FALSE
<replaceable>expression</replaceable> IS UNKNOWN
<replaceable>expression</replaceable> IS NOT UNKNOWN
</synopsis>
    These will always return true or false, never a null value, even when the
    operand is null.
    A null input is treated as the logical value <quote>unknown</>.
    Notice that <literal>IS UNKNOWN</> and <literal>IS NOT UNKNOWN</> are
    effectively the same as <literal>IS NULL</literal> and
    <literal>IS NOT NULL</literal>, respectively, except that the input
    expression must be of Boolean type.