func.sgml

 <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> NULLIF(<replaceable class="parameter">input</replaceable>,<replaceable class="parameter">value</replaceable>) </entry>
	<entry> <replaceable class="parameter">input</replaceable> or NULL </entry>
	<entry> return NULL if <replaceable class="parameter">input</replaceable> = <replaceable class="parameter">value</replaceable> </entry>
	<entry> NULLIF(<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>
       </row>
       <row>
	<entry> reltime(timespan) </entry>
	<entry> reltime </entry>
	<entry> convert to reltime </entry>
	<entry> reltime('4 hrs'::timespan) </entry>
       </row>
       <row>
	<entry> timespan(reltime) </entry>
	<entry> timespan </entry>
	<entry> convert to timespan </entry>
	<entry> timespan('4 hours'::reltime) </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    For the
    <function>date_part</function> and <function>date_trunc</function>
    functions, arguments can be
    `year', `month', `day', `hour', `minute', and `second',
    as well as the more specialized quantities
    `decade', `century', `millenium', `millisecond', and `microsecond'.
    <function>date_part</function> allows `dow'
    to return day of week and `epoch' to return seconds since 1970
    (for <type>datetime</type>)
    or 'epoch' to return total elapsed seconds (for <type>timespan</type>).
   </para>
  </sect1>

  
  <sect1>

   <title id="formatting-funcs"> Formatting Functions </title>

   <note>
    <title>Author</title>
    <para>
     Written by 
     <ulink url="mailto:zakkr@zf.jcu.cz">Karel Zak</ulink>
     on 2000-01-24.
    </para>
   </note>

   <para>
    Formatting functions provide a powerful set of tools for converting 
    various datetypes (date/time, int, float, numeric) to formatted strings 
    and reverse convert from formatted strings to original datetypes. 
   </para>

   <para>
    <table tocentry="1">
     <title>Formatting Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> to_char(datetime, text) </entry>
	<entry> text </entry>
	<entry> convert datetime to string </entry>
	<entry> to_char('now'::datetime, 'HH12:MI:SS') </entry>
       </row>
       <row>
	<entry> to_char(timestamp, text) </entry>
	<entry> text </entry>
	<entry> convert timestamp to string </entry>
	<entry> to_char( now(), 'HH12:MI:SS') </entry>
       </row>
       <row>
	<entry> to_char(int, text) </entry>
	<entry> text </entry>
	<entry> convert int4/int8 to string </entry>
	<entry> to_char(125, '999') </entry>
       </row>
       <row>
	<entry> to_char(float, text) </entry>
	<entry> text </entry>
	<entry> convert float4/float8 to string </entry>
	<entry> to_char(125.8, '999D9') </entry>
       </row>
       <row>
	<entry> to_char(numeric, text) </entry>
	<entry> text </entry>
	<entry> convert numeric to string </entry>
	<entry> to_char(-125.8, '999D99S') </entry>
       </row>       
       <row>
	<entry> to_datetime(text, text) </entry>
	<entry> datetime </entry>
	<entry> convert string to datetime </entry>
	<entry> to_datetime('05 Dec 2000 13', 'DD Mon YYYY HH') </entry>
       </row>
       <row>
	<entry> to_date(text, text) </entry>
	<entry> date </entry>
	<entry> convert string to date </entry>
	<entry> to_date('05 Dec 2000', 'DD Mon YYYY') </entry>
       </row>
       <row>
	<entry> to_timestamp(text, text) </entry>
	<entry> date </entry>
	<entry> convert string to timestamp </entry>
	<entry> to_timestamp('05 Dec 2000', 'DD Mon YYYY') </entry>
       </row>
       <row>
	<entry> to_number(text, text) </entry>
	<entry> numeric </entry>
	<entry> convert string to numeric </entry>
	<entry> to_number('12,454.8-', '99G999D9S') </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    For all formatting functions is second argument format-picture. 
   </para>

   <para>
    <table tocentry="1">
     <title>Format-pictures for date/time to_char() version.</title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Format-picture</entry>
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> HH </entry>
	<entry> hour of day (01-12) </entry>
       </row>
       <row>
	<entry> HH12 </entry>
	<entry> hour of day (01-12) </entry>
       </row>       
       <row>
	<entry> MI </entry>
	<entry> minute (00-59) </entry>
       </row>   
       <row>
	<entry> SS </entry>
	<entry> socond (00-59) </entry>
       </row>
       <row>
	<entry> SSSS </entry>
	<entry> seconds past midnight (0-86399) </entry>
       </row>
       <row>
	<entry> Y,YYY </entry>
	<entry> year (4 and more digits) with comma </entry>
       </row>
       <row>
	<entry> YYYY </entry>
	<entry> year (4 and more digits) </entry>
       </row>
       <row>
	<entry> YYY </entry>
	<entry> last 3 digits of year </entry>
       </row>
       <row>
	<entry> YY </entry>
	<entry> last 2 digits of year </entry>
       </row>
       <row>
	<entry> Y </entry>
	<entry> last digit of year </entry>
       </row>
       <row>
	<entry> MONTH </entry>
	<entry> full month name (9-letters) - all characters is upper  </entry>
       </row>
       <row>
	<entry> Month </entry>
	<entry> full month name (9-letters) - first character is upper  </entry>
       </row>
       <row>
	<entry> month </entry>
	<entry> full month name (9-letters) - all characters is lower </entry>
       </row>
       <row>
	<entry> MON </entry>
	<entry> abbreviated month name (3-letters) - all characters is upper </entry>
       </row>
       <row>
	<entry> Mon </entry>
	<entry> abbreviated month name (3-letters) - first character is upper </entry>
       </row>
       <row>
	<entry> mon </entry>
	<entry> abbreviated month name (3-letters) - all characters is lower </entry>
       </row>
       <row>
	<entry> MM </entry>
	<entry> month (01-12) </entry>
       </row>
       <row>
	<entry> DAY </entry>
	<entry> full day name (9-letters) - all characters is upper </entry>
       </row>
       <row>
	<entry> Day </entry>
	<entry> full day name (9-letters) - first character is upper </entry>
       </row>
       <row>
	<entry> day </entry>
	<entry> full day name (9-letters) - all characters is lower </entry>
       </row>
       <row>
	<entry> DY </entry>
	<entry> abbreviated day name (3-letters) - all characters is upper </entry>
       </row>
       <row>
	<entry> Dy </entry>
	<entry> abbreviated day name (3-letters) - first character is upper </entry>
       </row>
       <row>
	<entry> dy </entry>
	<entry> abbreviated day name (3-letters) - all characters is upper </entry>
       </row>
       <row>
	<entry> DDD </entry>
	<entry> day of year (001-366) </entry>
       </row>
       <row>
	<entry> DD </entry>
	<entry> day of month (01-31) </entry>
       </row>
       <row>
	<entry> D </entry>
	<entry> day of week (1-7; SUN=1) </entry>
       </row>
       <row>
	<entry> W </entry>
	<entry> week of month </entry>
       </row> 
       <row>
	<entry> WW </entry>
	<entry> week number of year </entry>
       </row>
       <row>
	<entry> CC </entry>
	<entry> century (2-digits) </entry>
       </row>
       <row>
	<entry> J </entry>
	<entry> julian day (days since January 1, 4712 BC) </entry>
       </row>
       <row>
	<entry> Q </entry>
	<entry> quarter </entry>
       </row>
       <row>
	<entry> RM </entry>
	<entry> month in roman numeral (I-XII; I=JAN) </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    All format-pictures	allow use suffixes (postfix / prefix). The suffix is 
    always valid for a near format-picture. The 'FX' is global prefix only.      
   </para>

   <para>
    <table tocentry="1">
     <title>Suffixes for format-pictures for date/time to_char() version.</title>
     <tgroup cols="3">
      <thead>
       <row>
	<entry>Suffix</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> FM </entry>
	<entry> fill mode - prefix </entry>
	<entry> FMMonth </entry>
       </row>
       <row>
	<entry> TH </entry>
	<entry> upper ordinal number - postfix </entry>
	<entry> DDTH </entry>
       </row>	
       <row>
	<entry> th </entry>
	<entry> lower ordinal number - postfix </entry>
	<entry> DDTH </entry>
       </row>
       <row>
	<entry> FX </entry>
	<entry> FX - (Fixed format) global format-picture switch. 
	 The TO_DATETIME / TO_DATE skip blank space if this option is
	 not use. Must by used as first item in formt-picture. </entry>
	<entry> FX Month DD Day </entry>
       </row>	
       <row>
	<entry> SP </entry>
	<entry> spell mode (not implement now)</entry>
	<entry> DDSP </entry>
       </row>       
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
     '\' - must be use as double \\, example '\\HH\\MI\\SS' 
   </para>
   <para>
     '"' - string between a quotation marks is skipen and not is parsed. 
     If you want write '"' to output you must use \\", example '\\"YYYY Month\\"'.
   </para>
   <para>
     text - the PostgreSQL's to_char() support text without '"', but string 
     between a quotation marks is fastly and you have guarantee, that a text 
     not will interpreted as a keyword (format-picture), exapmle '"Hello Year: "YYYY'.  
   </para>
   
   <para>
    <table tocentry="1">
     <title>Format-pictures for number (int/float/numeric) to_char() version.</title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Format-picture</entry>
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> 9 </entry>
	<entry> return value with the specified number of digits, and if digit is
	 not available use blank space </entry>
       </row>
       <row>
	<entry> 0 </entry>
	<entry> as 9, but instead blank space use zero </entry>
       </row>
       <row>
	<entry> . (period) </entry>
	<entry> decimal point </entry>
       </row>       
       <row>
	<entry> , (comma) </entry>
	<entry> group (thousand) separator </entry>
       </row>
       <row>
	<entry> PR </entry>
	<entry> return negative value in angle brackets </entry>
       </row>
       <row>
	<entry> S </entry>
	<entry> return negatice value with minus sign (use locales) </entry>
       </row>
       <row>
	<entry> L </entry>
	<entry> currency symbol (use locales) </entry>
       </row>
       <row>
	<entry> D </entry>
	<entry> decimal point (use locales) </entry>
       </row>
       <row>
	<entry> G </entry>
	<entry> group separator (use locales) </entry>
       </row>
       <row>
	<entry> MI </entry>
	<entry> return minus sign on specified position (if number < 0) </entry>
       </row>
       <row>
	<entry> PL </entry>
	<entry> return plus sign on specified position (if number > 0) - PostgreSQL extension </entry>
       </row>
       <row>
	<entry> SG </entry>
	<entry> return plus/minus sign on specified position - PostgreSQL extension </entry>
       </row>
       <row>
	<entry> RN </entry>
	<entry> return number as roman number (number must be between 1 and 3999) </entry>
       </row>
       <row>
	<entry> TH or th </entry>
	<entry> convert number to ordinal number (not convert numbers under zero and decimal numbers) - PostgreSQL extension </entry>
       </row>
       <row>
	<entry> V </entry>
	<entry> arg1 * (10 ^ n); - return a value multiplied by 10^n (where 'n' is number of '9's after the 'V'). 
	        The to_char() not support use 'V' and decimal poin together, example "99.9V99".   </entry>
       </row>
       <row>
	<entry> EEEE </entry>
	<entry> science numbers. Now not supported. </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
      Note: A sign formatted via 'SG', 'PL' or 'MI' is not anchor in number;
      to_char(-12, 'S9999') produce: <ProgramListing> '  -12' </ProgramListing>, 
      but to_char(-12, 'MI9999') produce: <ProgramListing> '-  12' </ProgramListing>. 
      The Oracle not allow use 'MI' ahead of '9', in the Oracle must be it always
      after '9'.
   </para>   

   <para>
    <table tocentry="1">
     <title> The to_char() examples. </title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Input</entry>
	<entry>Output</entry>
       </row>
      </thead>
      <tbody>
       <row>
        <entry> to_char(now(), 'Day, HH12:MI:SS') </entry>
        <entry><ProgramListing> 'Tuesday  , 05:39:18' </ProgramListing></entry>
       </row>   
       <row>
        <entry> to_char(now(), 'FMDay, HH12:MI:SS') </entry>
        <entry><ProgramListing> 'Tuesday, 05:39:18' </ProgramListing></entry>
       </row>          
       <row>
        <entry> to_char( -0.1, '99.99') </entry>
        <entry><ProgramListing> ' -.10' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( -0.1, 'FM9.99') </entry>
        <entry><ProgramListing> '-.1' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 0.1, '0.9') </entry>
        <entry><ProgramListing> ' 0.1' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 12, '9990999.9') </entry>
        <entry><ProgramListing> '    0012.0' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 12, 'FM9990999.9') </entry>
        <entry><ProgramListing> '0012' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 485, '999') </entry>
        <entry><ProgramListing> ' 485' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( -485, '999') </entry>
        <entry><ProgramListing> '-485' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 485, '9 9 9') </entry>
        <entry><ProgramListing> ' 4 8 5' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 1485,	'9,999') </entry>	
        <entry><ProgramListing> ' 1,485' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 1485,	'9G999') </entry>
        <entry><ProgramListing> ' 1 485' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 148.5, '999.999') </entry>
        <entry><ProgramListing> ' 148.500' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 148.5, '999D999') </entry>
        <entry><ProgramListing> ' 148,500' </ProgramListing></entry>	 
       </row>
       <row>
        <entry> to_char( 3148.5,'9G999D999') </entry>
        <entry><ProgramListing> ' 3 148,500' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( -485,	'999S') </entry>
        <entry><ProgramListing> '485-'	</ProgramListing></entry>
       </row>
       <row>		
        <entry> to_char( -485,	'999MI') </entry>
        <entry><ProgramListing> '485-'	</ProgramListing></entry>	
       </row>
       <row>
        <entry> to_char( 485,	'999MI') </entry>
        <entry><ProgramListing> '485' </ProgramListing></entry>		
       </row>
       <row>
        <entry> to_char( 485,	'PL999') </entry>
        <entry><ProgramListing> '+485'	</ProgramListing></entry>	
       </row>
       <row>		
        <entry> to_char( 485,	'SG999') </entry>
        <entry><ProgramListing> '+485'	</ProgramListing></entry>	
       </row>
       <row>
        <entry> to_char( -485,	'SG999') </entry>
        <entry><ProgramListing> '-485'	</ProgramListing></entry>	
       </row>
       <row>
        <entry> to_char( -485,	'9SG99') </entry>
        <entry><ProgramListing> '4-85'	</ProgramListing></entry>	
       </row>
       <row>
        <entry> to_char( -485,	'999PR') </entry>
        <entry><ProgramListing> '<485>' </ProgramListing></entry>		
       </row>
       <row>
        <entry> to_char( 485,	'L999')	</entry>
        <entry><ProgramListing> 'DM 485' </ProgramListing></entry>	 
       </row>
       <row>
        <entry> to_char( 485,	'RN') </entry>		
        <entry><ProgramListing> '        CDLXXXV' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 485,	'FMRN')	</entry>	
        <entry><ProgramListing> 'CDLXXXV' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 5.2,	'FMRN')	</entry>
        <entry><ProgramListing> 'V' </ProgramListing></entry>		
       </row>
       <row>
        <entry> to_char( 482,	'999th') </entry>
        <entry><ProgramListing> ' 482nd' </ProgramListing></entry>				
       </row>
       <row>
        <entry> to_char( 485,	'"Good number:"999') </entry>
        <entry><ProgramListing> 'Good number: 485' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 485.8, '"Pre-decimal:"999" Post-decimal:" .999') </entry>
        <entry><ProgramListing> 'Pre-decimal: 485 Post-decimal: .800' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 12, '99V999') </entry>		
        <entry><ProgramListing> ' 12000' </ProgramListing></entry>
       </row>
       <row>
        <entry> to_char( 12.4, '99V999') </entry>
        <entry><ProgramListing> ' 12400' </ProgramListing></entry>
       </row>
       <row>		
        <entry> to_char( 12.45, '99V9') </entry>
        <entry><ProgramListing> ' 125' </ProgramListing></entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

  </sect1>



  <sect1>
   <title>Geometric Functions</title>

   <para>
    The geometric types point, box, lseg, line, path, polygon, and
    circle have a large set of native support functions.
   </para>

   <para>
    <table tocentry="1">
     <title>Geometric Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> area(box) </entry>
	<entry> float8 </entry>
	<entry> area of box </entry>
	<entry> area('((0,0),(1,1))'::box) </entry>
       </row>
       <row>
	<entry> area(circle) </entry>
	<entry> float8 </entry>
	<entry> area of circle </entry>
	<entry> area('((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> box(box,box) </entry>
	<entry> box </entry>
	<entry> boxes to intersection box </entry>
	<entry> box('((0,0),(1,1))','((0.5,0.5),(2,2))') </entry>
       </row>
       <row>
	<entry> center(box) </entry>
	<entry> point </entry>
	<entry> center of object </entry>
	<entry> center('((0,0),(1,2))'::box) </entry>
       </row>
       <row>
	<entry> center(circle) </entry>
	<entry> point </entry>
	<entry> center of object </entry>
	<entry> center('((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> diameter(circle) </entry>
	<entry> float8 </entry>
	<entry> diameter of circle </entry>
	<entry> diameter('((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> height(box) </entry>
	<entry> float8 </entry>
	<entry> vertical size of box </entry>
	<entry> height('((0,0),(1,1))'::box) </entry>
       </row>
       <row>
	<entry> isclosed(path) </entry>
	<entry> bool </entry>
	<entry> a closed path? </entry>
	<entry> isclosed('((0,0),(1,1),(2,0))'::path) </entry>
       </row>
       <row>
	<entry> isopen(path) </entry>
	<entry> bool </entry>
	<entry> an open path? </entry>
	<entry> isopen('[(0,0),(1,1),(2,0)]'::path) </entry>
       </row>
       <row>
	<entry> length(lseg) </entry>
	<entry> float8 </entry>
	<entry> length of line segment </entry>
	<entry> length('((-1,0),(1,0))'::lseg) </entry>
       </row>
       <row>
	<entry> length(path) </entry>
	<entry> float8 </entry>
	<entry> length of path </entry>
	<entry> length('((0,0),(1,1),(2,0))'::path) </entry>
       </row>
       <row>
	<entry> pclose(path) </entry>
	<entry> path </entry>
	<entry> convert path to closed </entry>
	<entry> popen('[(0,0),(1,1),(2,0)]'::path) </entry>
       </row>
       <row>
	<entry> point(lseg,lseg) </entry>
	<entry> point </entry>
	<entry> intersection </entry>
	<entry> point('((-1,0),(1,0))'::lseg,'((-2,-2),(2,2))'::lseg) </entry>
       </row>
       <row>
	<entry> points(path) </entry>
	<entry> int4 </entry>
	<entry> number of points </entry>
	<entry> points('[(0,0),(1,1),(2,0)]'::path) </entry>
       </row>
       <row>
	<entry> popen(path) </entry>
	<entry> path </entry>
	<entry> convert path to open </entry>
	<entry> popen('((0,0),(1,1),(2,0))'::path) </entry>
       </row>
       <row>
	<entry> radius(circle) </entry>
	<entry> float8 </entry>
	<entry> radius of circle </entry>
	<entry> radius('((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> width(box) </entry>
	<entry> float8 </entry>
	<entry> horizontal size </entry>
	<entry> width('((0,0),(1,1))'::box) </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    <table tocentry="1">
     <title>Geometric Type Conversion Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> box(circle) </entry>
	<entry> box </entry>
	<entry> convert circle to box </entry>
	<entry> box('((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> box(point,point) </entry>
	<entry> box </entry>
	<entry> convert points to box </entry>
	<entry> box('(0,0)'::point,'(1,1)'::point) </entry>
       </row>
       <row>
	<entry> box(polygon) </entry>
	<entry> box </entry>
	<entry> convert polygon to box </entry>
	<entry> box('((0,0),(1,1),(2,0))'::polygon) </entry>
       </row>
       <row>
	<entry> circle(box) </entry>
	<entry> circle </entry>
	<entry> convert to circle </entry>
	<entry> circle('((0,0),(1,1))'::box) </entry>
       </row>
       <row>
	<entry> circle(point,float8) </entry>
	<entry> circle </entry>
	<entry> convert to circle </entry>
	<entry> circle('(0,0)'::point,2.0) </entry>
       </row>
       <row>
	<entry> lseg(box) </entry>
	<entry> lseg </entry>
	<entry> convert diagonal to lseg </entry>
	<entry> lseg('((-1,0),(1,0))'::box) </entry>
       </row>
       <row>
	<entry> lseg(point,point) </entry>
	<entry> lseg </entry>
	<entry> convert to lseg </entry>
	<entry> lseg('(-1,0)'::point,'(1,0)'::point) </entry>
       </row>
       <row>
	<entry> path(polygon) </entry>
	<entry> point </entry>
	<entry> convert to path </entry>
	<entry> path('((0,0),(1,1),(2,0))'::polygon) </entry>
       </row>
       <row>
	<entry> point(circle) </entry>
	<entry> point </entry>
	<entry> convert to point (center) </entry>
	<entry> point('((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> point(lseg,lseg) </entry>
	<entry> point </entry>
	<entry> convert to point (intersection) </entry>
	<entry> point('((-1,0),(1,0))'::lseg, '((-2,-2),(2,2))'::lseg) </entry>
       </row>
       <row>
	<entry> point(polygon) </entry>
	<entry> point </entry>
	<entry> center of polygon </entry>
	<entry> point('((0,0),(1,1),(2,0))'::polygon) </entry>
       </row>
       <row>
	<entry> polygon(box) </entry>
	<entry> polygon </entry>
	<entry> convert to polygon with 12 points </entry>
	<entry> polygon('((0,0),(1,1))'::box) </entry>
       </row>
       <row>
	<entry> polygon(circle) </entry>
	<entry> polygon </entry>
	<entry> convert to 12-point polygon </entry>
	<entry> polygon('((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> polygon(<replaceable class="parameter">npts</replaceable>,circle) </entry>
	<entry> polygon </entry>
	<entry> convert to <replaceable class="parameter">npts</replaceable> polygon </entry>
	<entry> polygon(12,'((0,0),2.0)'::circle) </entry>
       </row>
       <row>
	<entry> polygon(path) </entry>
	<entry> polygon </entry>
	<entry> convert to polygon </entry>
	<entry> polygon('((0,0),(1,1),(2,0))'::path) </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    <table tocentry="1">
     <title>Geometric Upgrade Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> isoldpath(path) </entry>
	<entry> path </entry>
	<entry> test path for pre-v6.1 form </entry>
	<entry> isoldpath('(1,3,0,0,1,1,2,0)'::path) </entry>
       </row>
       <row>
	<entry> revertpoly(polygon) </entry>
	<entry> polygon </entry>
	<entry> convert pre-v6.1 polygon </entry>
	<entry> revertpoly('((0,0),(1,1),(2,0))'::polygon) </entry>
       </row>
       <row>
	<entry> upgradepath(path) </entry>
	<entry> path </entry>
	<entry> convert pre-v6.1 path </entry>
	<entry> upgradepath('(1,3,0,0,1,1,2,0)'::path) </entry>
       </row>
       <row>
	<entry> upgradepoly(polygon) </entry>
	<entry> polygon </entry>
	<entry> convert pre-v6.1 polygon </entry>
	<entry> upgradepoly('(0,1,2,0,1,0)'::polygon) </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>
  </sect1>

  <sect1>
   <title id="cidr-funcs">IP V4 Functions</title>

   <para>
    <table tocentry="1">
     <title><productname>Postgres</productname>IP V4 Functions</title>
     <tgroup cols="4">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry> broadcast(cidr) </entry>
	<entry> text </entry>
	<entry> construct broadcast address as text </entry>
	<entry> broadcast('192.168.1.5/24') </entry>
       </row>
       <row>
	<entry> broadcast(inet) </entry>
	<entry> text </entry>
	<entry> construct broadcast address as text </entry>
	<entry> broadcast('192.168.1.5/24') </entry>
       </row>
       <row>
	<entry> host(inet) </entry>
	<entry> text </entry>
	<entry> extract host address as text </entry>
	<entry> host('192.168.1.5/24') </entry>
       </row>
       <row>
	<entry> masklen(cidr) </entry>
	<entry> int4 </entry>
	<entry> calculate netmask length </entry>
	<entry> masklen('192.168.1.5/24') </entry>
       </row>
       <row>
	<entry> masklen(inet) </entry>
	<entry> int4 </entry>
	<entry> calculate netmask length </entry>
	<entry> masklen('192.168.1.5/24') </entry>
       </row>
       <row>
	<entry> netmask(inet) </entry>
	<entry> text </entry>
	<entry> construct netmask as text </entry>
	<entry> netmask('192.168.1.5/24') </entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

  </sect1>

 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->