func.sgml

关系型数据库 Postgresql 6.5.2

 <chapter id="functions">
  <title id="functions-title">Functions</title>

  <abstract>
   <para>
    Describes the built-in functions available
    in <productname>Postgres</productname>.
   </para>
  </abstract>

  <para>
   Many data types have functions available for conversion to other related types.
   In addition, there are some type-specific functions. Some functions are also
   available through operators and may be documented as operators only.
  </para>

  <sect1>
   <title id="sql-funcs">SQL Functions</title>

   <para>
    <quote><acronym>SQL</acronym> functions</quote> are constructs
    defined by the <acronym>SQL92</acronym> standard which have
    function-like syntax but which can not be implemented as simple
    functions. 
   </para>

   <para>
    <table tocentry="1">
     <title>SQL Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> COALESCE(<replaceable class="parameter">list</replaceable>) </entry>
	<entry> non-NULL </entry>
	<entry> return first non-NULL value in list </entry>
	<entry> COALESCE(<replaceable class="parameter">r"</replaceable>le>, <replaceable
	  class="parameter">c2</replaceable> + 5, 0) </entry>
       </row>
       <row>
	<entry> IFNULL(<replaceable class="parameter">input</replaceable>,<replaceable class="parameter">non-NULL substitute</replaceable>) </entry>
	<entry> non-NULL </entry>
	<entry> return second argument if first is NULL </entry>
	<entry> IFNULL(<replaceable class="parameter">c1</replaceable>, 'N/A')</entry>
       </row>
       <row>
	<entry> CASE WHEN <replaceable class="parameter">expr</replaceable> THEN <replaceable class="parameter">expr</replaceable> [...] ELSE <replaceable class="parameter">expr</replaceable> END </entry>
	<entry> <replaceable class="parameter">expr</replaceable> </entry>
	<entry> return expression for first true clause </entry>
	<entry> CASE WHEN <replaceable class="parameter">c1</replaceable> = 1 THEN 'match' ELSE 'no match' END </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>
  </sect1>

  <sect1>
   <title id="math-funcs">Mathematical Functions</title>

   <para>
    <table tocentry="1">
     <title>Mathematical Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> dexp(float8) </entry>
	<entry> float8 </entry>
	<entry> raise e to the specified exponent </entry>
	<entry> dexp(2.0) </entry>
       </row>
       <row>
	<entry> dpow(float8,float8) </entry>
	<entry> float8 </entry>
	<entry> raise a number to the specified exponent </entry>
	<entry> dpow(2.0, 16.0) </entry>
       </row>
       <row>
	<entry> float(int) </entry>
	<entry> float8 </entry>
	<entry> convert integer to floating point </entry>
	<entry> float(2) </entry>
       </row>
       <row>
	<entry> float4(int) </entry>
	<entry> float4 </entry>
	<entry> convert integer to floating point </entry>
	<entry> float4(2) </entry>
       </row>
       <row>
	<entry> integer(float) </entry>
	<entry> int </entry>
	<entry> convert floating point to integer </entry>
	<entry> integer(2.0) </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>
  </sect1>

  <sect1>
   <title>String Functions</title>

   <para>
    SQL92 defines string functions with specific syntax. Some of these
    are implemented using other <productname>Postgres</productname> functions.
    The supported string types for <acronym>SQL92</acronym> are
    <type>char</type>, <type>varchar</type>, and <type>text</type>.
   </para>

   <para>
    <table tocentry="1">
     <title><acronym>SQL92</acronym> String Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> char_length(string) </entry>
	<entry> int4 </entry>
	<entry> length of string </entry>
	<entry> char_length('jose') </entry>
       </row>
       <row>
	<entry> character_length(string) </entry>
	<entry> int4 </entry>
	<entry> length of string </entry>
	<entry> char_length('jose') </entry>
       </row>
       <row>
	<entry> lower(string) </entry>
	<entry> string </entry>
	<entry> convert string to lower case </entry>
	<entry> lower('TOM') </entry>
       </row>
       <row>
	<entry> octet_length(string) </entry>
	<entry> int4 </entry>
	<entry> storage length of string </entry>
	<entry> octet_length('jose') </entry>
       </row>
       <row>
	<entry> position(string in string) </entry>
	<entry> int4 </entry>
	<entry> location of specified substring </entry>
	<entry> position('o' in 'Tom') </entry>
       </row>
       <row>
	<entry> substring(string [from int] [for int]) </entry>
	<entry> string </entry>
	<entry> extract specified substring </entry>
	<entry> substring('Tom' from 2 for 2) </entry>
       </row>
       <row>
	<entry> trim([leading|trailing|both] [string] from string) </entry>
	<entry> string </entry>
	<entry> trim characters from string </entry>
	<entry> trim(both 'x' from 'xTomx') </entry>
       </row>
       <row>
	<entry> upper(text) </entry>
	<entry> text </entry>
	<entry> convert text to upper case </entry>
	<entry> upper('tom') </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    Many additional string functions are available for text, varchar(), and char() types.
    Some are used internally to implement the SQL92 string functions listed above.
   </para>

   <para>
    <table tocentry="1">
     <title>String Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> char(text) </entry>
	<entry> char </entry>
	<entry> convert text to char type </entry>
	<entry> char('text string') </entry>
       </row>
       <row>
	<entry> char(varchar) </entry>
	<entry> char </entry>
	<entry> convert varchar to char type </entry>
	<entry> char(varchar 'varchar string') </entry>
       </row>
       <row>
	<entry> initcap(text) </entry>
	<entry> text </entry>
	<entry> first letter of each word to upper case </entry>
	<entry> initcap('thomas') </entry>
       </row>
       <row>
	<entry> lpad(text,int,text) </entry>
	<entry> text </entry>
	<entry> left pad string to specified length </entry>
	<entry> lpad('hi',4,'??') </entry>
       </row>
       <row>
	<entry> ltrim(text,text) </entry>
	<entry> text </entry>
	<entry> left trim characters from text </entry>
	<entry> ltrim('xxxxtrim','x') </entry>
       </row>
       <row>
	<entry> textpos(text,text) </entry>
	<entry> text </entry>
	<entry> locate specified substring </entry>
	<entry> position('high','ig') </entry>
       </row>
       <row>
	<entry> rpad(text,int,text) </entry>
	<entry> text </entry>
	<entry> right pad string to specified length </entry>
	<entry> rpad('hi',4,'x') </entry>
       </row>
       <row>
	<entry> rtrim(text,text) </entry>
	<entry> text </entry>
	<entry> right trim characters from text </entry>
	<entry> rtrim('trimxxxx','x') </entry>
       </row>
       <row>
	<entry> substr(text,int[,int]) </entry>
	<entry> text </entry>
	<entry> extract specified substring </entry>
	<entry> substr('hi there',3,5) </entry>
       </row>
       <row>
	<entry> text(char) </entry>
	<entry> text </entry>
	<entry> convert char to text type </entry>
	<entry> text('char string') </entry>
       </row>
       <row>
	<entry> text(varchar) </entry>
	<entry> text </entry>
	<entry> convert varchar to text type </entry>
	<entry> text(varchar 'varchar string') </entry>
       </row>
       <row>
	<entry> translate(text,from,to) </entry>
	<entry> text </entry>
	<entry> convert character in string </entry>
	<entry> translate('12345', '1', 'a') </entry>
       </row>
       <row>
	<entry> varchar(char) </entry>
	<entry> varchar </entry>
	<entry> convert char to varchar type </entry>
	<entry> varchar('char string') </entry>
       </row>
       <row>
	<entry> varchar(text) </entry>
	<entry> varchar </entry>
	<entry> convert text to varchar type </entry>
	<entry> varchar('text string') </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    Most functions explicitly defined for text will work for char() and varchar() arguments.
   </para>
  </sect1>

  <sect1>
   <title>Date/Time Functions</title>

   <para>
    The date/time functions provide a powerful set of tools
    for manipulating various date/time types.
   </para>

   <para>
    <table tocentry="1">
     <title>Date/Time Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> abstime(datetime) </entry>
	<entry> abstime </entry>
	<entry> convert to abstime </entry>
	<entry> abstime('now'::datetime) </entry>
       </row>
       <row>
	<entry> age(datetime,datetime) </entry>
	<entry> timespan </entry>
	<entry> preserve months and years </entry>
	<entry> age('now','1957-06-13'::datetime) </entry>
       </row>
       <row>
	<entry> datetime(abstime) </entry>
	<entry> datetime </entry>
	<entry> convert to datetime </entry>
	<entry> datetime('now'::abstime) </entry>
       </row>
       <row>
	<entry> datetime(date) </entry>
	<entry> datetime </entry>
	<entry> convert to datetime </entry>
	<entry> datetime('today'::date) </entry>
       </row>
       <row>
	<entry> datetime(date,time) </entry>
	<entry> datetime </entry>
	<entry> convert to datetime </entry>
	<entry> datetime('1998-02-24'::datetime, '23:07'::time); </entry>
  </row>
       <row>
	<entry> date_part(text,datetime) </entry>
	<entry> float8 </entry>
	<entry> portion of date </entry>
	<entry> date_part('dow','now'::datetime) </entry>
       </row>
       <row>
	<entry> date_part(text,timespan) </entry>
	<entry> float8 </entry>
	<entry> portion of time </entry>
	<entry> date_part('hour','4 hrs 3 mins'::timespan) </entry>
       </row>
       <row>
	<entry> date_trunc(text,datetime) </entry>
	<entry> datetime </entry>
	<entry> truncate date </entry>
	<entry> date_trunc('month','now'::abstime) </entry>
       </row>
       <row>
	<entry> isfinite(abstime) </entry>
	<entry> bool </entry>
	<entry> a finite time? </entry>
	<entry> isfinite('now'::abstime) </entry>
       </row>
       <row>
	<entry> isfinite(datetime) </entry>
	<entry> bool </entry>
	<entry> a finite time? </entry>
	<entry> isfinite('now'::datetime) </entry>
       </row>
       <row>
	<entry> isfinite(timespan) </entry>
	<entry> bool </entry>
	<entry> a finite time? </entry>
	<entry> isfinite('4 hrs'::timespan) </entry>