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(rle, c2 + 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>,
	else <replaceable class="parameter">input</replaceable>
	</entry>
	<entry>NULLIF(c1, '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 WHEN clause</entry>
	<entry>CASE WHEN c1 = 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>abs(float8)</entry>
	<entry>float8</entry>
	<entry>absolute value</entry>
	<entry>abs(-17.4)</entry>
       </row>
       <row>
	<entry>degrees(float8)</entry>
	<entry>float8</entry>
	<entry>radians to degrees</entry>
	<entry>degrees(0.5)</entry>
       </row>
       <row>
	<entry>exp(float8)</entry>
	<entry>float8</entry>
	<entry>raise e to the specified exponent</entry>
	<entry>exp(2.0)</entry>
       </row>
       <row>
	<entry>ln(float8)</entry>
	<entry>float8</entry>
	<entry>natural logarithm</entry>
	<entry>ln(2.0)</entry>
       </row>
       <row>
	<entry>log(float8)</entry>
	<entry>float8</entry>
	<entry>base 10 logarithm</entry>
	<entry>log(2.0)</entry>
       </row>
       <row>
	<entry>pi()</entry>
	<entry>float8</entry>
	<entry>fundamental constant</entry>
	<entry>pi()</entry>
       </row>
       <row>
	<entry>pow(float8,float8)</entry>
	<entry>float8</entry>
	<entry>raise a number to the specified exponent</entry>
	<entry>pow(2.0, 16.0)</entry>
       </row>
       <row>
	<entry>radians(float8)</entry>
	<entry>float8</entry>
	<entry>degrees to radians</entry>
	<entry>radians(45.0)</entry>
       </row>
       <row>
	<entry>round(float8)</entry>
	<entry>float8</entry>
	<entry>round to nearest integer</entry>
	<entry>round(42.4)</entry>
       </row>
       <row>
	<entry>sqrt(float8)</entry>
	<entry>float8</entry>
	<entry>square root</entry>
	<entry>sqrt(2.0)</entry>
       </row>
       <row>
	<entry>cbrt(float8)</entry>
	<entry>float8</entry>
	<entry>cube root</entry>
	<entry>cbrt(27.0)</entry>
       </row>
       <row>
	<entry>trunc(float8)</entry>
	<entry>float8</entry>
	<entry>truncate (towards zero)</entry>
	<entry>trunc(42.4)</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>

   <para>
    Most of the functions listed for FLOAT8 are also available for
    type NUMERIC.
   </para>

   <para>
    <table tocentry="1">
     <title>Transcendental 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>acos(float8)</entry>
	<entry>float8</entry>
	<entry>arccosine</entry>
	<entry>acos(10.0)</entry>
       </row>
       <row>
	<entry>asin(float8)</entry>
	<entry>float8</entry>
	<entry>arcsine</entry>
	<entry>asin(10.0)</entry>
       </row>
       <row>
	<entry>atan(float8)</entry>
	<entry>float8</entry>
	<entry>arctangent</entry>
	<entry>atan(10.0)</entry>
       </row>
       <row>
	<entry>atan2(float8,float8)</entry>
	<entry>float8</entry>
	<entry>arctangent</entry>
	<entry>atan3(10.0,20.0)</entry>
       </row>
       <row>
	<entry>cos(float8)</entry>
	<entry>float8</entry>
	<entry>cosine</entry>
	<entry>cos(0.4)</entry>
       </row>
       <row>
	<entry>cot(float8)</entry>
	<entry>float8</entry>
	<entry>cotangent</entry>
	<entry>cot(20.0)</entry>
       </row>
       <row>
	<entry>sin(float8)</entry>
	<entry>float8</entry>
	<entry>sine</entry>
	<entry>cos(0.4)</entry>
       </row>
       <row>
	<entry>tan(float8)</entry>
	<entry>float8</entry>
	<entry>tangent</entry>
	<entry>tan(0.4)</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(timestamp)</entry>
	<entry>abstime</entry>
	<entry>convert to abstime</entry>
	<entry>abstime(timestamp 'now')</entry>
       </row>
       <row>
	<entry>age(timestamp)</entry>
	<entry>interval</entry>
	<entry>preserve months and years</entry>
	<entry>age(timestamp '1957-06-13')</entry>
       </row>
       <row>
	<entry>age(timestamp,timestamp)</entry>
	<entry>interval</entry>
	<entry>preserve months and years</entry>
	<entry>age('now', timestamp '1957-06-13')</entry>
       </row>
       <row>
	<entry>timestamp(abstime)</entry>
	<entry>timestamp</entry>
	<entry>convert to timestamp</entry>
	<entry>timestamp(abstime 'now')</entry>
       </row>
       <row>
	<entry>timestamp(date)</entry>
	<entry>timestamp</entry>
	<entry>convert to timestamp</entry>
	<entry>timestamp(date 'today')</entry>
       </row>
       <row>
	<entry>timestamp(date,time)</entry>
	<entry>timestamp</entry>
	<entry>convert to timestamp</entry>
	<entry>timestamp(timestamp '1998-02-24',time '23:07');</entry>
       </row>
       <row>
	<entry>date_part(text,timestamp)</entry>
	<entry>float8</entry>
	<entry>portion of date</entry>
	<entry>date_part('dow',timestamp 'now')</entry>
       </row>
       <row>
	<entry>date_part(text,interval)</entry>
	<entry>float8</entry>
	<entry>portion of time</entry>
	<entry>date_part('hour',interval '4 hrs 3 mins')</entry>
       </row>
       <row>
	<entry>date_trunc(text,timestamp)</entry>
	<entry>timestamp</entry>
	<entry>truncate date</entry>
	<entry>date_trunc('month',abstime 'now')</entry>
       </row>
       <row>
	<entry>isfinite(abstime)</entry>
	<entry>bool</entry>
	<entry>a finite time?</entry>
	<entry>isfinite(abstime 'now')</entry>
       </row>
       <row>
	<entry>isfinite(timestamp)</entry>
	<entry>bool</entry>
	<entry>a finite time?</entry>
	<entry>isfinite(timestamp 'now')</entry>
       </row>
       <row>
	<entry>isfinite(interval)</entry>
	<entry>bool</entry>
	<entry>a finite time?</entry>
	<entry>isfinite(interval '4 hrs')</entry>
       </row>
       <row>
	<entry>reltime(interval)</entry>
	<entry>reltime</entry>
	<entry>convert to reltime</entry>
	<entry>reltime(interval '4 hrs')</entry>
       </row>
       <row>
	<entry>interval(reltime)</entry>
	<entry>interval</entry>
	<entry>convert to interval</entry>
	<entry>interval(reltime '4 hours')</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>timestamp</type>)
    or 'epoch' to return total elapsed seconds (for <type>interval</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>
    The <productname>Postgres</productname>
    formatting functions provide a powerful set of tools for converting 
    various datetypes (date/time, int, float, numeric) to formatted strings 
    and for converting from formatted strings to specific datetypes. 

    <note>
     <para>
      The second argument for all formatting functions is a template to
      be used for the conversion.
     </para>
    </note>
   </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(timestamp, text)</entry>
	<entry>text</entry>
	<entry>convert timestamp to string</entry>
	<entry>to_char(timestamp '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(numeric '-125.8', '999D99S')</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>
    <table tocentry="1">
     <title>Templates for date/time conversions</title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Template</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>second (00-59)</entry>
       </row>
       <row>
	<entry>SSSS</entry>
	<entry>seconds past midnight (0-86399)</entry>
       </row>
       <row>
	<entry>AM or A.M. or PM or P.M.</entry>
	<entry>meridian indicator (upper case)</entry>
       </row>
       <row>
	<entry>am or a.m. or pm or p.m.</entry>
	<entry>meridian indicator (lower case)</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>BC or B.C. or AD or A.D.</entry>
	<entry>year indicator (upper case)</entry>
       </row>
       <row>
	<entry>bc or b.c. or ad or a.d.</entry>
	<entry>year indicator (lower case)</entry>
       </row>
       <row>
	<entry>MONTH</entry>
	<entry>full upper case month name (9 chars)</entry>
       </row>
       <row>
	<entry>Month</entry>
	<entry>full mixed case month name (9 chars)</entry>
       </row>
       <row>
	<entry>month</entry>
	<entry>full lower case month name (9 chars)</entry>
       </row>
       <row>
	<entry>MON</entry>
	<entry>upper case abbreviated month name (3 chars)</entry>
       </row>
       <row>
	<entry>Mon</entry>
	<entry>abbreviated mixed case month name (3 chars)</entry>
       </row>
       <row>
	<entry>mon</entry>
	<entry>abbreviated lower case month name (3 chars)</entry>
       </row>
       <row>
	<entry>MM</entry>
	<entry>month (01-12)</entry>
       </row>
       <row>
	<entry>DAY</entry>
	<entry>full upper case day name (9 chars)</entry>
       </row>
       <row>
	<entry>Day</entry>
	<entry>full mixed case day name (9 chars)</entry>
       </row>
       <row>
	<entry>day</entry>
	<entry>full lower case day name (9 chars)</entry>
       </row>
       <row>
	<entry>DY</entry>
	<entry>abbreviated upper case day name (3 chars)</entry>
       </row>
       <row>
	<entry>Dy</entry>
	<entry>abbreviated mixed case day name (3 chars)</entry>
       </row>
       <row>
	<entry>dy</entry>
	<entry>abbreviated lower case day name (3 chars)</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 Numerals (I-XII; I=JAN) - upper case</entry>
       </row>
       <row>
	<entry>rn</entry>
	<entry>month in Roman Numerals (I-XII; I=JAN) - lower case</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    All templates allow the use of prefix and suffix modifiers. Modifiers are
    always valid for use in templates. The prefix 'FX' is a global modifier only.      
   </para>

   <para>
    <table tocentry="1">
     <title>Suffixes for templates for date/time to_char()</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 suffix</entry>
	<entry>DDTH</entry>
       </row>	
       <row>
	<entry>th</entry>
	<entry>lower ordinal number suffix</entry>
	<entry>DDTH</entry>
       </row>
       <row>
	<entry>FX</entry>
	<entry>FiXed format global option (see below)</entry>
	<entry>FX Month DD Day</entry>
       </row>	
       <row>
	<entry>SP</entry>
	<entry>spell mode (not yet implemented)</entry>
	<entry>DDSP</entry>
       </row>       
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    Usage notes:

    <itemizedlist>
     <listitem>
      <para>
       <function>to_timestamp</function> and <function>to_date</function>
       skip blank space if the <literal>FX</literal> option is
       not use. <literal>FX</literal> Must be specified as the first item
       in the template.
      </para>
     </listitem>

     <listitem>
      <para>
       '\' - must be use as double \\, example '\\HH\\MI\\SS' 
      </para>
     </listitem>

     <listitem>
      <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>
     </listitem>

     <listitem>
      <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>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    <table tocentry="1">
     <title>Templates for to_char(<replaceable>numeric</replaceable>)</title>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Template</entry>
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry>9</entry>
	<entry>value with the specified number of digits</entry>
       </row>
       <row>
	<entry>0</entry>
	<entry>value with leading zeros</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>negative value in angle brackets</entry>
       </row>
       <row>
	<entry>S</entry>
	<entry>negative 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>minus sign on specified position (if number < 0)</entry>
       </row>
       <row>
	<entry>PL</entry>
	<entry>plus sign on specified position (if number > 0)</entry>
       </row>
       <row>
	<entry>SG</entry>
	<entry>plus/minus sign on specified position</entry>
       </row>
       <row>
	<entry>RN</entry>
	<entry>roman numeral (input between 1 and 3999)</entry>
       </row>
       <row>
	<entry>TH or th</entry>
	<entry>convert to ordinal number</entry>
       </row>
       <row>
	<entry>V</entry>
	<entry>Shift <replaceable>n</replaceable> digits (see
	 notes)</entry>
       </row>
       <row>
	<entry>EEEE</entry>
	<entry>science numbers. Now not supported.</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    Usage notes:

    <itemizedlist>
     <listitem>
      <para>
       A sign formatted using 'SG', 'PL' or 'MI' is not an anchor in
       the number; for example,
       to_char(-12, 'S9999') produces <literal>'  -12'</literal>,
       but to_char(-12, 'MI9999') produces <literal>'-  12'</literal>.
       The Oracle implementation does not allow the use of
       <literal>MI</literal> ahead of <literal>9</literal>, but rather
       requires that <literal>9</literal> preceeds
       <literal>MI</literal>.
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>PL</literal>, <literal>SG</literal>, and
       <literal>TH</literal> are <productname>Postgres</productname>
       extensions. 
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>9</literal> specifies a value with the same number of 
       digits as there are <literal>9</literal>s. If a digit is
       not available use blank space.
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>TH</literal> does not convert values less than zero
       and does not convert decimal numbers. <literal>TH</literal> is
       a <productname>Postgres</productname> extension.
      </para>
     </listitem>

     <listitem>
      <para>
       <literal>V</literal> effectively
       multiplies the input values by
       <literal>10^<replaceable>n</replaceable></literal>, where
       <replaceable>n</replaceable> is the number of digits following
       <literal>V</literal>. 
       <function>to_char</function> does not support the use of
       <literal>V</literal> combined with a decimal point
       (e.g. "99.9V99" is not allowed).
      </para>
     </listitem>
    </itemizedlist>
   </para>   

   <para>
    <table tocentry="1">
     <title><function>to_char</function> 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><literal>'Tuesday  , 05:39:18'</literal></entry>
       </row>
       <row>
        <entry>to_char(now(),'FMDay, HH12:MI:SS')</entry>
        <entry><literal>'Tuesday, 05:39:18'</literal></entry>
       </row>          
       <row>
        <entry>to_char(-0.1,'99.99')</entry>
        <entry><literal>' -.10'</literal></entry>
       </row>
       <row>
        <entry>to_char(-0.1,'FM9.99')</entry>
        <entry><literal>'-.1'</literal></entry>
       </row>
       <row>
        <entry>to_char(0.1,'0.9')</entry>
        <entry><literal>' 0.1'</literal></entry>
       </row>
       <row>
        <entry>to_char(12,'9990999.9')</entry>
        <entry><literal>'    0012.0'</literal></entry>
       </row>
       <row>
        <entry>to_char(12,'FM9990999.9')</entry>
        <entry><literal>'0012'</literal></entry>
       </row>
       <row>
        <entry>to_char(485,'999')</entry>
        <entry><literal>' 485'</literal></entry>
       </row>
       <row>
        <entry>to_char(-485,'999')</entry>
        <entry><literal>'-485'</literal></entry>
       </row>
       <row>
        <entry>to_char(485,'9 9 9')</entry>
        <entry><literal>' 4 8 5'</literal></entry>
       </row>
       <row>
        <entry>to_char(1485,'9,999')</entry>
        <entry><literal>' 1,485'</literal></entry>
       </row>
       <row>
        <entry>to_char(1485,'9G999')</entry>
        <entry><literal>' 1 485'</literal></entry>
       </row>
       <row>
        <entry>to_char(148.5,'999.999')</entry>
        <entry><literal>' 148.500'</literal></entry>
       </row>
       <row>
        <entry>to_char(148.5,'999D999')</entry>
        <entry><literal>' 148,500'</literal></entry>	 
       </row>
       <row>
        <entry>to_char(3148.5,'9G999D999')</entry>
        <entry><literal>' 3 148,500'</literal></entry>
       </row>
       <row>
        <entry>to_char(-485,'999S')</entry>
        <entry><literal>'485-'</literal></entry>
       </row>
       <row>		
        <entry>to_char(-485,'999MI')</entry>
        <entry><literal>'485-'</literal></entry>	
       </row>
       <row>
        <entry>to_char(485,'999MI')</entry>
        <entry><literal>'485'</literal></entry>		
       </row>
       <row>
        <entry>to_char(485,'PL999')</entry>
        <entry><literal>'+485'</literal></entry>	
       </row>
       <row>		
        <entry>to_char(485,'SG999')</entry>
        <entry><literal>'+485'</literal></entry>	
       </row>
       <row>
        <entry>to_char(-485,'SG999')</entry>
        <entry><literal>'-485'</literal></entry>	
       </row>
       <row>
        <entry>to_char(-485,'9SG99')</entry>
        <entry><literal>'4-85'</literal></entry>	
       </row>
       <row>
        <entry>to_char(-485,'999PR')</entry>
        <entry><literal>'&lt;485&gt;'</literal></entry>		
       </row>
       <row>
        <entry>to_char(485,'L999')</entry>
        <entry><literal>'DM 485</literal></entry>	 
       </row>
       <row>
        <entry>to_char(485,'RN')</entry>		
        <entry><literal>'        CDLXXXV'</literal></entry>
       </row>
       <row>
        <entry>to_char(485,'FMRN')</entry>	
        <entry><literal>'CDLXXXV'</literal></entry>
       </row>
       <row>
        <entry>to_char(5.2,'FMRN')</entry>
        <entry><literal>V</literal></entry>		
       </row>
       <row>
        <entry>to_char(482,'999th')</entry>
        <entry><literal>' 482nd'</literal></entry>				
       </row>
       <row>
        <entry>to_char(485, '"Good number:"999')</entry>
        <entry><literal>'Good number: 485'</literal></entry>
       </row>
       <row>
        <entry>to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999')</entry>
        <entry><literal>'Pre-decimal: 485 Post-decimal: .800'</literal></entry>
       </row>
       <row>
        <entry>to_char(12,'99V999')</entry>		
        <entry><literal>' 12000'</literal></entry>
       </row>
       <row>
        <entry>to_char(12.4,'99V999')</entry>
        <entry><literal>' 12400'</literal></entry>
       </row>
       <row>		
        <entry>to_char(12.45, '99V9')</entry>
        <entry><literal>' 125'</literal></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(object)</entry>
	<entry>float8</entry>
	<entry>area of circle, ...</entry>
	<entry>area(box '((0,0),(1,1))')</entry>
       </row>
       <row>
	<entry>box(box,box)</entry>
	<entry>box</entry>
	<entry>boxes to intersection box</entry>
	<entry>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</entry>
       </row>
       <row>
	<entry>center(object)</entry>
	<entry>point</entry>
	<entry>center of circle, ...</entry>
	<entry>center(box '((0,0),(1,2))')</entry>
       </row>
       <row>
	<entry>diameter(circle)</entry>
	<entry>float8</entry>
	<entry>diameter of circle</entry>
	<entry>diameter(circle '((0,0),2.0)')</entry>
       </row>
       <row>
	<entry>height(box)</entry>
	<entry>float8</entry>
	<entry>vertical size of box</entry>
	<entry>height(box '((0,0),(1,1))')</entry>
       </row>
       <row>
	<entry>isclosed(path)</entry>
	<entry>bool</entry>
	<entry>a closed path?</entry>
	<entry>isclosed(path '((0,0),(1,1),(2,0))')</entry>
       </row>
       <row>
	<entry>isopen(path)</entry>
	<entry>bool</entry>
	<entry>an open path?</entry>
	<entry>isopen(path '[(0,0),(1,1),(2,0)]')</entry>
       </row>
       <row>
	<entry>length(object)</entry>
	<entry>float8</entry>
	<entry>length of line segment, ...</entry>
	<entry>length(path '((-1,0),(1,0))')</entry>
       </row>
       <row>
	<entry>length(path)</entry>
	<entry>float8</entry>
	<entry>length of path</entry>
	<entry>length(path '((0,0),(1,1),(2,0))')</entry>
       </row>
       <row>
	<entry>pclose(path)</entry>
	<entry>path</entry>
	<entry>convert path to closed</entry>
	<entry>popen(path '[(0,0),(1,1),(2,0)]')</entry>
       </row>
<!--
Not defined by this name. Implements the intersection operator '#'
       <row>
	<entry>point(lseg,lseg)</entry>
	<entry>point</entry>
	<entry>intersection</entry>
	<entry>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</entry>
       </row>
-->
       <row>
	<entry>npoint(path)</entry>
	<entry>int4</entry>
	<entry>number of points</entry>
	<entry>npoints(path '[(0,0),(1,1),(2,0)]')</entry>
       </row>
       <row>
	<entry>popen(path)</entry>
	<entry>path</entry>
	<entry>convert path to open path</entry>
	<entry>popen(path '((0,0),(1,1),(2,0))')</entry>
       </row>
       <row>
	<entry>radius(circle)</entry>
	<entry>float8</entry>
	<entry>radius of circle</entry>
	<entry>radius(circle '((0,0),2.0)')</entry>
       </row>
       <row>
	<entry>width(box)</entry>
	<entry>float8</entry>
	<entry>horizontal size</entry>
	<entry>width(box '((0,0),(1,1))')</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:
-->