func.sgml

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

      <row>
       <entry><literal><function>setseed</function>(<type>dp</type>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>set seed for subsequent <literal>random()</literal> calls</entry>
       <entry><literal>setseed(0.54823)</literal></entry>
       <entry><literal>1177314959</literal></entry>
      </row>

      <row>
       <entry><literal><function>sign</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>sign of the argument (-1, 0, +1)</entry>
       <entry><literal>sign(-8.4)</literal></entry>
       <entry><literal>-1</literal></entry>
      </row>

      <row>
       <entry><literal><function>sqrt</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>square root</entry>
       <entry><literal>sqrt(2.0)</literal></entry>
       <entry><literal>1.4142135623731</literal></entry>
      </row>

      <row>
       <entry><literal><function>trunc</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>truncate toward zero</entry>
       <entry><literal>trunc(42.8)</literal></entry>
       <entry><literal>42</literal></entry>
      </row>

      <row>
       <entry><literal><function>trunc</function>(<parameter>v</parameter> <type>numeric</type>, <parameter>s</parameter> <type>int</type>)</literal></entry>
       <entry><type>numeric</type></entry>
       <entry>truncate to <parameter>s</parameter> decimal places</entry>
       <entry><literal>trunc(42.4382, 2)</literal></entry>
       <entry><literal>42.43</literal></entry>
      </row>

      <row>
       <entry><literal><function>width_bucket</function>(<parameter>op</parameter> <type>numeric</type>, <parameter>b1</parameter> <type>numeric</type>, <parameter>b2</parameter> <type>numeric</type>, <parameter>count</parameter> <type>int</type>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>return the bucket to which <parameter>operand</> would
       be assigned in an equidepth histogram with <parameter>count</>
       buckets, an upper bound of <parameter>b1</>, and a lower bound
       of <parameter>b2</></entry>
       <entry><literal>width_bucket(5.35, 0.024, 10.06, 5)</literal></entry>
       <entry><literal>3</literal></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  <para>
   Finally, <xref linkend="functions-math-trig-table"> shows the
   available trigonometric functions.  All trigonometric functions
   take arguments and return values of type <type>double
   precision</type>.
  </para>

   <indexterm>
    <primary>acos</primary>
   </indexterm>
   <indexterm>
    <primary>asin</primary>
   </indexterm>
   <indexterm>
    <primary>atan</primary>
   </indexterm>
   <indexterm>
    <primary>atan2</primary>
   </indexterm>
   <indexterm>
    <primary>cos</primary>
   </indexterm>
   <indexterm>
    <primary>cot</primary>
   </indexterm>
   <indexterm>
    <primary>sin</primary>
   </indexterm>
   <indexterm>
    <primary>tan</primary>
   </indexterm>

   <table id="functions-math-trig-table">
    <title>Trigonometric Functions</title>

    <tgroup cols="2">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Description</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><literal><function>acos</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>inverse cosine</entry>
      </row>

      <row>
       <entry><literal><function>asin</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>inverse sine</entry>
      </row>

      <row>
       <entry><literal><function>atan</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>inverse tangent</entry>
      </row>

      <row>
       <entry><literal><function>atan2</function>(<replaceable>x</replaceable>,
        <replaceable>y</replaceable>)</literal></entry>
       <entry>inverse tangent of
        <literal><replaceable>x</replaceable>/<replaceable>y</replaceable></literal></entry>
      </row>

      <row>
       <entry><literal><function>cos</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>cosine</entry>
      </row>

      <row>
       <entry><literal><function>cot</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>cotangent</entry>
      </row>

      <row>
       <entry><literal><function>sin</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>sine</entry>
      </row>

      <row>
       <entry><literal><function>tan</function>(<replaceable>x</replaceable>)</literal></entry>
       <entry>tangent</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

  </sect1>


  <sect1 id="functions-string">
   <title>String Functions and Operators</title>

   <para>
    This section describes functions and operators for examining and
    manipulating string values.  Strings in this context include values
    of all the types <type>character</type>, <type>character
     varying</type>, and <type>text</type>.  Unless otherwise noted, all
    of the functions listed below work on all of these types, but be
    wary of potential effects of the automatic padding when using the
    <type>character</type> type.  Generally, the functions described
    here also work on data of non-string types by converting that data
    to a string representation first.  Some functions also exist
    natively for the bit-string types.
   </para>

   <para>
    <acronym>SQL</acronym> defines some string functions with a special syntax where
    certain key words rather than commas are used to separate the
    arguments.  Details are in <xref linkend="functions-string-sql">.
    These functions are also implemented using the regular syntax for
    function invocation.  (See <xref linkend="functions-string-other">.)
   </para>

   <indexterm>
    <primary>bit_length</primary>
   </indexterm>
   <indexterm>
    <primary>char_length</primary>
   </indexterm>
   <indexterm>
    <primary>convert</primary>
   </indexterm>
   <indexterm>
    <primary>lower</primary>
   </indexterm>
   <indexterm>
    <primary>octet_length</primary>
   </indexterm>
   <indexterm>
    <primary>overlay</primary>
   </indexterm>
   <indexterm>
    <primary>position</primary>
   </indexterm>
   <indexterm>
    <primary>substring</primary>
   </indexterm>
   <indexterm>
    <primary>trim</primary>
   </indexterm>
   <indexterm>
    <primary>upper</primary>
   </indexterm>

   <table id="functions-string-sql">
    <title><acronym>SQL</acronym> String Functions and Operators</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>  
      </row>
     </thead>

     <tbody>
      <row>
       <entry><literal><parameter>string</parameter> <literal>||</literal>
        <parameter>string</parameter></literal></entry>
       <entry> <type>text</type> </entry>
       <entry>
        String concatenation
        <indexterm>
         <primary>character string</primary>
         <secondary>concatenation</secondary>
        </indexterm>
       </entry>
       <entry><literal>'Post' || 'greSQL'</literal></entry>
       <entry><literal>PostgreSQL</literal></entry>
      </row>

      <row>
       <entry><literal><function>bit_length</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>Number of bits in string</entry>
       <entry><literal>bit_length('jose')</literal></entry>
       <entry><literal>32</literal></entry>
      </row>

      <row>
       <entry><literal><function>char_length</function>(<parameter>string</parameter>)</literal> or <literal><function>character_length</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>
        Number of characters in string
        <indexterm>
         <primary>character string</primary>
         <secondary>length</secondary>
        </indexterm>
        <indexterm>
         <primary>length</primary>
         <secondary sortas="character string">of a character string</secondary>
         <see>character string, length</see>
        </indexterm>
       </entry>
       <entry><literal>char_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry><literal><function>convert</function>(<parameter>string</parameter>
       using <parameter>conversion_name</parameter>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Change encoding using specified conversion name.  Conversions
        can be defined by <command>CREATE CONVERSION</command>.  Also
        there are some pre-defined conversion names. See <xref
        linkend="conversion-names"> for available conversion
        names.
       </entry>
       <entry><literal>convert('PostgreSQL' using iso_8859_1_to_utf8)</literal></entry>
       <entry><literal>'PostgreSQL'</literal> in UTF8 (Unicode, 8-bit) encoding</entry>
      </row>

      <row>
       <entry><literal><function>lower</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Convert string to lower case</entry>
       <entry><literal>lower('TOM')</literal></entry>
       <entry><literal>tom</literal></entry>
      </row>

      <row>
       <entry><literal><function>octet_length</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>Number of bytes in string</entry>
       <entry><literal>octet_length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry><literal><function>overlay</function>(<parameter>string</parameter> placing <parameter>string</parameter> from <type>int</type> <optional>for <type>int</type></optional>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Replace substring
       </entry>
       <entry><literal>overlay('Txxxxas' placing 'hom' from 2 for 4)</literal></entry>
       <entry><literal>Thomas</literal></entry>
      </row>

      <row>
       <entry><literal><function>position</function>(<parameter>substring</parameter> in <parameter>string</parameter>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>Location of specified substring</entry>
       <entry><literal>position('om' in 'Thomas')</literal></entry>
       <entry><literal>3</literal></entry>
      </row>

      <row>
       <entry><literal><function>substring</function>(<parameter>string</parameter> <optional>from <type>int</type></optional> <optional>for <type>int</type></optional>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring
       </entry>
       <entry><literal>substring('Thomas' from 2 for 3)</literal></entry>
       <entry><literal>hom</literal></entry>
      </row>

      <row>
       <entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring matching POSIX regular expression
       </entry>
       <entry><literal>substring('Thomas' from '...$')</literal></entry>
       <entry><literal>mas</literal></entry>
      </row>

      <row>
       <entry><literal><function>substring</function>(<parameter>string</parameter> from <replaceable>pattern</replaceable> for <replaceable>escape</replaceable>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring matching <acronym>SQL</acronym> regular
        expression
       </entry>
       <entry><literal>substring('Thomas' from '%#"o_a#"_' for '#')</literal></entry>
       <entry><literal>oma</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>trim</function>(<optional>leading | trailing | both</optional>
        <optional><parameter>characters</parameter></optional> from
        <parameter>string</parameter>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only the
        <parameter>characters</parameter> (a space by default) from the
        start/end/both ends of the <parameter>string</parameter>
       </entry>
       <entry><literal>trim(both 'x' from 'xTomxx')</literal></entry>
       <entry><literal>Tom</literal></entry>
      </row>

      <row>
       <entry><literal><function>upper</function>(<parameter>string</parameter>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Convert string to uppercase</entry>
       <entry><literal>upper('tom')</literal></entry>
       <entry><literal>TOM</literal></entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Additional string manipulation functions are available and are
    listed in <xref linkend="functions-string-other">.  Some of them are used internally to implement the
    <acronym>SQL</acronym>-standard string functions listed in <xref linkend="functions-string-sql">.
   </para>

   <indexterm>
    <primary>ascii</primary>
   </indexterm>
   <indexterm>
    <primary>btrim</primary>
   </indexterm>
   <indexterm>
    <primary>chr</primary>
   </indexterm>
   <indexterm>
    <primary>decode</primary>
   </indexterm>
   <indexterm>
    <primary>encode</primary>
   </indexterm>
   <indexterm>
    <primary>initcap</primary>
   </indexterm>
   <indexterm>
    <primary>lpad</primary>
   </indexterm>
   <indexterm>
    <primary>ltrim</primary>