func.sgml

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

   </para>
  </sect1>

  <sect1 id="functions-math">
   <title>Mathematical Functions and Operators</title>

   <para>
    Mathematical operators are provided for many
    <productname>PostgreSQL</productname> types. For types without
    common mathematical conventions for all possible permutations 
    (e.g., date/time types) we
    describe the actual behavior in subsequent sections.
   </para>

   <para>
    <xref linkend="functions-math-op-table"> shows the available mathematical operators.
   </para>

   <table id="functions-math-op-table">
    <title>Mathematical Operators</title>

    <tgroup cols="4">
     <thead>
      <row>
       <entry>Operator</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry> <literal>+</literal> </entry>
       <entry>addition</entry>
       <entry><literal>2 + 3</literal></entry>
       <entry><literal>5</literal></entry>
      </row>

      <row>
       <entry> <literal>-</literal> </entry>
       <entry>subtraction</entry>
       <entry><literal>2 - 3</literal></entry>
       <entry><literal>-1</literal></entry>
      </row>

      <row>
       <entry> <literal>*</literal> </entry>
       <entry>multiplication</entry>
       <entry><literal>2 * 3</literal></entry>
       <entry><literal>6</literal></entry>
      </row>

      <row>
       <entry> <literal>/</literal> </entry>
       <entry>division (integer division truncates results)</entry>
       <entry><literal>4 / 2</literal></entry>
       <entry><literal>2</literal></entry>
      </row>

      <row>
       <entry> <literal>%</literal> </entry>
       <entry>modulo (remainder)</entry>
       <entry><literal>5 % 4</literal></entry>
       <entry><literal>1</literal></entry>
      </row>

      <row>
       <entry> <literal>^</literal> </entry>
       <entry>exponentiation</entry>
       <entry><literal>2.0 ^ 3.0</literal></entry>
       <entry><literal>8</literal></entry>
      </row>

      <row>
       <entry> <literal>|/</literal> </entry>
       <entry>square root</entry>
       <entry><literal>|/ 25.0</literal></entry>
       <entry><literal>5</literal></entry>
      </row>

      <row>
       <entry> <literal>||/</literal> </entry>
       <entry>cube root</entry>
       <entry><literal>||/ 27.0</literal></entry>
       <entry><literal>3</literal></entry>
      </row>

      <row>
       <entry> <literal>!</literal> </entry>
       <entry>factorial</entry>
       <entry><literal>5 !</literal></entry>
       <entry><literal>120</literal></entry>
      </row>

      <row>
       <entry> <literal>!!</literal> </entry>
       <entry>factorial (prefix operator)</entry>
       <entry><literal>!! 5</literal></entry>
       <entry><literal>120</literal></entry>
      </row>

      <row>
       <entry> <literal>@</literal> </entry>
       <entry>absolute value</entry>
       <entry><literal>@ -5.0</literal></entry>
       <entry><literal>5</literal></entry>
      </row>

      <row>
       <entry> <literal>&amp;</literal> </entry>
       <entry>bitwise AND</entry>
       <entry><literal>91 &amp; 15</literal></entry>
       <entry><literal>11</literal></entry>
      </row>

      <row>
       <entry> <literal>|</literal> </entry>
       <entry>bitwise OR</entry>
       <entry><literal>32 | 3</literal></entry>
       <entry><literal>35</literal></entry>
      </row>

      <row>
       <entry> <literal>#</literal> </entry>
       <entry>bitwise XOR</entry>
       <entry><literal>17 # 5</literal></entry>
       <entry><literal>20</literal></entry>
      </row>

      <row>
       <entry> <literal>~</literal> </entry>
       <entry>bitwise NOT</entry>
       <entry><literal>~1</literal></entry>
       <entry><literal>-2</literal></entry>
      </row>

      <row>
       <entry> <literal>&lt;&lt;</literal> </entry>
       <entry>bitwise shift left</entry>
       <entry><literal>1 &lt;&lt; 4</literal></entry>
       <entry><literal>16</literal></entry>
      </row>

      <row>
       <entry> <literal>&gt;&gt;</literal> </entry>
       <entry>bitwise shift right</entry>
       <entry><literal>8 &gt;&gt; 2</literal></entry>
       <entry><literal>2</literal></entry>
      </row>

     </tbody>
    </tgroup>
   </table>

   <para>
    The bitwise operators work only on integral data types, whereas
    the others are available for all numeric data types.  The bitwise
    operators are also available for the bit
    string types <type>bit</type> and <type>bit varying</type>, as
    shown in <xref linkend="functions-bit-string-op-table">.
   </para>

  <para>
   <xref linkend="functions-math-func-table"> shows the available
   mathematical functions.  In the table, <literal>dp</literal>
   indicates <type>double precision</type>.  Many of these functions
   are provided in multiple forms with different argument types.
   Except where noted, any given form of a function returns the same
   data type as its argument.
   The functions working with <type>double precision</type> data are mostly
   implemented on top of the host system's C library; accuracy and behavior in
   boundary cases may therefore vary depending on the host system.
  </para>

   <indexterm>
    <primary>abs</primary>
   </indexterm>
   <indexterm>
    <primary>cbrt</primary>
   </indexterm>
   <indexterm>
    <primary>ceiling</primary>
   </indexterm>
   <indexterm>
    <primary>degrees</primary>
   </indexterm>
   <indexterm>
    <primary>exp</primary>
   </indexterm>
   <indexterm>
    <primary>floor</primary>
   </indexterm>
   <indexterm>
    <primary>ln</primary>
   </indexterm>
   <indexterm>
    <primary>log</primary>
   </indexterm>
   <indexterm>
    <primary>mod</primary>
   </indexterm>
   <indexterm>
    <primary>&pi;</primary>
   </indexterm>
   <indexterm>
    <primary>power</primary>
   </indexterm>
   <indexterm>
    <primary>radians</primary>
   </indexterm>
   <indexterm>
    <primary>random</primary>
   </indexterm>
   <indexterm>
    <primary>round</primary>
   </indexterm>
   <indexterm>
    <primary>setseed</primary>
   </indexterm>
   <indexterm>
    <primary>sign</primary>
   </indexterm>
   <indexterm>
    <primary>sqrt</primary>
   </indexterm>
   <indexterm>
    <primary>trunc</primary>
   </indexterm>
   <indexterm>
    <primary>width_bucket</primary>
   </indexterm>

   <table id="functions-math-func-table">
    <title>Mathematical Functions</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><function>abs</>(<replaceable>x</replaceable>)</literal></entry>
       <entry>(same as <replaceable>x</>)</entry>
       <entry>absolute value</entry>
       <entry><literal>abs(-17.4)</literal></entry>
       <entry><literal>17.4</literal></entry>
      </row>

      <row>
       <entry><literal><function>cbrt</function>(<type>dp</type>)</literal></entry>
       <entry><type>dp</type></entry>
       <entry>cube root</entry>
       <entry><literal>cbrt(27.0)</literal></entry>
       <entry><literal>3</literal></entry>
      </row>

      <row>
       <entry><literal><function>ceil</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>smallest integer not less than argument</entry>
       <entry><literal>ceil(-42.8)</literal></entry>
       <entry><literal>-42</literal></entry>
      </row>

      <row>
       <entry><literal><function>ceiling</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>smallest integer not less than argument (alias for <function>ceil</function>)</entry>
       <entry><literal>ceiling(-95.3)</literal></entry>
       <entry><literal>-95</literal></entry>
      </row>

      <row>
       <entry><literal><function>degrees</function>(<type>dp</type>)</literal></entry>
       <entry><type>dp</type></entry>
       <entry>radians to degrees</entry>
       <entry><literal>degrees(0.5)</literal></entry>
       <entry><literal>28.6478897565412</literal></entry>
      </row>

      <row>
       <entry><literal><function>exp</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>exponential</entry>
       <entry><literal>exp(1.0)</literal></entry>
       <entry><literal>2.71828182845905</literal></entry>
      </row>

      <row>
       <entry><literal><function>floor</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>largest integer not greater than argument</entry>
       <entry><literal>floor(-42.8)</literal></entry>
       <entry><literal>-43</literal></entry>
      </row>

      <row>
       <entry><literal><function>ln</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>natural logarithm</entry>
       <entry><literal>ln(2.0)</literal></entry>
       <entry><literal>0.693147180559945</literal></entry>
      </row>

      <row>
       <entry><literal><function>log</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>base 10 logarithm</entry>
       <entry><literal>log(100.0)</literal></entry>
       <entry><literal>2</literal></entry>
      </row>

      <row>
       <entry><literal><function>log</function>(<parameter>b</parameter> <type>numeric</type>,
        <parameter>x</parameter> <type>numeric</type>)</literal></entry>
       <entry><type>numeric</type></entry>
       <entry>logarithm to base <parameter>b</parameter></entry>
       <entry><literal>log(2.0, 64.0)</literal></entry>
       <entry><literal>6.0000000000</literal></entry>
      </row>

      <row>
       <entry><literal><function>mod</function>(<parameter>y</parameter>,
        <parameter>x</parameter>)</literal></entry>
       <entry>(same as argument types)</entry>
       <entry>remainder of <parameter>y</parameter>/<parameter>x</parameter></entry>
       <entry><literal>mod(9,4)</literal></entry>
       <entry><literal>1</literal></entry>
      </row>

      <row>
       <entry><literal><function>pi</function>()</literal></entry>
       <entry><type>dp</type></entry>
       <entry><quote>&pi;</quote> constant</entry>
       <entry><literal>pi()</literal></entry>
       <entry><literal>3.14159265358979</literal></entry>
      </row>

      <row>
       <entry><literal><function>power</function>(<parameter>a</parameter> <type>dp</type>,
        <parameter>b</parameter> <type>dp</type>)</literal></entry>
       <entry><type>dp</type></entry>
       <entry><parameter>a</> raised to the power of <parameter>b</parameter></entry>
       <entry><literal>power(9.0, 3.0)</literal></entry>
       <entry><literal>729</literal></entry>
      </row>

      <row>
       <entry><literal><function>power</function>(<parameter>a</parameter> <type>numeric</type>,
        <parameter>b</parameter> <type>numeric</type>)</literal></entry>
       <entry><type>numeric</type></entry>
       <entry><parameter>a</> raised to the power of <parameter>b</parameter></entry>
       <entry><literal>power(9.0, 3.0)</literal></entry>
       <entry><literal>729</literal></entry>
      </row>

      <row>
       <entry><literal><function>radians</function>(<type>dp</type>)</literal></entry>
       <entry><type>dp</type></entry>
       <entry>degrees to radians</entry>
       <entry><literal>radians(45.0)</literal></entry>
       <entry><literal>0.785398163397448</literal></entry>
      </row>

      <row>
       <entry><literal><function>random</function>()</literal></entry>
       <entry><type>dp</type></entry>
       <entry>random value between 0.0 and 1.0</entry>
       <entry><literal>random()</literal></entry>
       <entry></entry>
      </row>

      <row>
       <entry><literal><function>round</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>
       <entry>(same as input)</entry>
       <entry>round to nearest integer</entry>
       <entry><literal>round(42.4)</literal></entry>
       <entry><literal>42</literal></entry>
      </row>

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