path: root/doc/src/sgml/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 id="sql-functions">
   <title>SQL Functions</title>

   <para>
    <firstterm><acronym>SQL</acronym> functions</firstterm> 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 id="math-functions">
   <title>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>
       <row>
	<entry>random()</entry>
	<entry>float8</entry>
	<entry>random value in the range 0.0 to 1.0</entry>
	<entry>random()</entry>
       </row>
       <row>
	<entry>setseed(float8)</entry>
	<entry>int</entry>
	<entry>set seed for subsequent random() calls</entry>
	<entry>setseed(0.54823)</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>atan2(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 id="string-functions">
   <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>ascii(text)</entry>
	<entry>int</entry>
	<entry>returns the decimal representation of the first character from text</entry>
	<entry>ascii('x')</entry>
       </row>
       <row>
	<entry>btrim(text,set)</entry>
	<entry>text</entry>
	<entry>both (left and right) trim characters from text</entry>
	<entry>btrim('xxxtrimxxx','x')</entry>
       </row>
       <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>chr(int)</entry>
	<entry>text</entry>
	<entry>returns the character having the binary equivalent to int</entry>
	<entry>chr(65)</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>repeat(text,int)</entry>
	<entry>text</entry>
	<entry>repeat text by int</entry>
	<entry>repeat('Pg', 4)</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>textpos(text,text)</entry>
	<entry>text</entry>
	<entry>locate specified substring</entry>
	<entry>position('high','ig')</entry>
       </row>
       <row>
	<entry>to_ascii(text [,name|int])</entry>
	<entry>text</entry>
	<entry>convert text from multibyte encoding to ASCII</entry>
	<entry>to_ascii('Karel')</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>
   <para>
    The to_ascii() support conversion from LATIN1, LATIN2, WIN1250 (CP1250) only.
   </para>
  </sect1>

  <sect1 id="datetime-functions">
   <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>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>interval(reltime)</entry>
	<entry>interval</entry>
	<entry>convert to interval</entry>
	<entry>interval(reltime '4 hours')</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>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>to_char(timestamp,text)</entry>
	<entry>text</entry>
	<entry>convert to string</entry>
	<entry>to_char(timestamp '1998-02-24','DD');</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

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

  
  <sect1 id="formatting-functions">
   <title>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>HH24</entry>
	<entry>hour of day (00-23)</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 (1-5) where first week start on the first day of the month</entry>
       </row> 
       <row>
	<entry>WW</entry>
	<entry>week number of year (1-53) where first week start on the first day of the year</entry>
       </row>
       <row>
	<entry>IW</entry>
	<entry>ISO 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>rm</entry>
	<entry>month in Roman Numerals (I-XII; I=JAN) - lower case</entry>
       </row>
       <row>
	<entry>TZ</entry>
	<entry>timezone string - upper case (not supported in the to_timestamp())</entry>
       </row>
       <row>
	<entry>tz</entry>
	<entry>timezone string - lower case (not supported in the to_timestamp())</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
    '<literal>FX</literal>' 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 multiple blank space in converted string if the <literal>FX</literal> option 
       is not used. <literal>FX</literal> must be specified as the first item
       in the template; for example 
       <literal>to_timestamp('2000    JUN','YYYY MON')</literal> is right, but
       <literal>to_timestamp('2000    JUN','FXYYYY MON')</literal> returns error,
       because to_timestamp() expects one blank space only.
      </para>
     </listitem>

     <listitem>
      <para>
       Backslash ("<literal>\</literal>") must be specified with a double backslash
       ("<literal>\\</literal>"); for example <literal>'\\HH\\MI\\SS'</literal>.
      </para>
     </listitem>

     <listitem>
      <para>
       A double quote (<quote><literal>"</literal></quote>) between
       quotation marks is skipped and is not parsed.  If you want to
       write a double quote to output you must preceed it with a
       double backslash (<literal>'\\"</literal>), for example
       <literal>'\\"YYYY Month\\"'</literal>.
      </para>
     </listitem>

     <listitem>
      <para>
       <function>to_char</function> supports text without a leading
       double quote but any string 
       between a quotation marks is rapidly handled and you are
       guaranteed that it will not be interpreted as a template
       keyword (example: <literal>'"Hello Year: "YYYY'</literal>).
      </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 id="geometric-functions">
   <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 item</entry>
	<entry>area(box '((0,0),(1,1))')</entry>
       </row>
       <row>
	<entry>box(box,box)</entry>
	<entry>box</entry>
	<entry>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 item</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 item</entry>
	<entry>length(path '((-1,0),(1,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>circle to box</entry>
	<entry>box('((0,0),2.0)'::circle)</entry>
       </row>
       <row>
	<entry>box(point,point)</entry>
	<entry>box</entry>
	<entry>points to box</entry>
	<entry>box('(0,0)'::point,'(1,1)'::point)</entry>
       </row>
       <row>
	<entry>box(polygon)</entry>
	<entry>box</entry>
	<entry>polygon to box</entry>
	<entry>box('((0,0),(1,1),(2,0))'::polygon)</entry>
       </row>
       <row>
	<entry>circle(box)</entry>
	<entry>circle</entry>
	<entry>to circle</entry>
	<entry>circle('((0,0),(1,1))'::box)</entry>
       </row>
       <row>
	<entry>circle(point,float8)</entry>
	<entry>circle</entry>
	<entry>point to circle</entry>
	<entry>circle('(0,0)'::point,2.0)</entry>
       </row>
       <row>
	<entry>lseg(box)</entry>
	<entry>lseg</entry>
	<entry>box diagonal to lseg</entry>
	<entry>lseg('((-1,0),(1,0))'::box)</entry>
       </row>
       <row>
	<entry>lseg(point,point)</entry>
	<entry>lseg</entry>
	<entry>points to lseg</entry>
	<entry>lseg('(-1,0)'::point,'(1,0)'::point)</entry>
       </row>
       <row>
	<entry>path(polygon)</entry>
	<entry>point</entry>
	<entry>polygon to path</entry>
	<entry>path('((0,0),(1,1),(2,0))'::polygon)</entry>
       </row>
       <row>
	<entry>point(circle)</entry>
	<entry>point</entry>
	<entry>center</entry>
	<entry>point('((0,0),2.0)'::circle)</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>point(polygon)</entry>
	<entry>point</entry>
	<entry>center</entry>
	<entry>point('((0,0),(1,1),(2,0))'::polygon)</entry>
       </row>
       <row>
	<entry>polygon(box)</entry>
	<entry>polygon</entry>
	<entry>12 point polygon</entry>
	<entry>polygon('((0,0),(1,1))'::box)</entry>
       </row>
       <row>
	<entry>polygon(circle)</entry>
	<entry>polygon</entry>
	<entry>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><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>path to polygon</entry>
	<entry>polygon('((0,0),(1,1),(2,0))'::path)</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>
  </sect1>

  <sect1 id="net-functions">
   <title>Network Address Type Functions</title>

   <para>
    <table tocentry="1" id="cidr-inet-functions">
     <title><type>cidr</> and <type>inet</> Functions</title>
     <tgroup cols="5">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
	<entry>Result</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry>broadcast(inet)</entry>
	<entry>inet</entry>
	<entry>broadcast address for network</entry>
	<entry>broadcast('192.168.1.5/24')</entry>
	<entry>192.168.1.255/24</entry>
       </row>
       <row>
	<entry>host(inet)</entry>
	<entry>text</entry>
	<entry>extract IP address as text</entry>
	<entry>host('192.168.1.5/24')</entry>
	<entry>192.168.1.5</entry>
       </row>
       <row>
	<entry>masklen(inet)</entry>
	<entry>integer</entry>
	<entry>extract netmask length</entry>
	<entry>masklen('192.168.1.5/24')</entry>
	<entry>24</entry>
       </row>
       <row>
	<entry>netmask(inet)</entry>
	<entry>inet</entry>
	<entry>construct netmask for network</entry>
	<entry>netmask('192.168.1.5/24')</entry>
	<entry>255.255.255.0</entry>
       </row>
       <row>
	<entry>network(inet)</entry>
	<entry>cidr</entry>
	<entry>extract network part of address</entry>
	<entry>network('192.168.1.5/24')</entry>
	<entry>192.168.1/24</entry>
       </row>
       <row>
	<entry>text(inet)</entry>
	<entry>text</entry>
	<entry>extract IP address and masklen as text</entry>
	<entry>text(inet '192.168.1.5')</entry>
	<entry>192.168.1.5/32</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    All of the functions for <type>inet</type> can be applied to
    <type>cidr</type> values as well.  The <function>host</>() and
    <function>text</>() functions are primarily intended to offer
    alternative display formats.
   </para>

   <para>
    <table tocentry="1" id="macaddr-functions">
     <title><type>macaddr</> Functions</title>
     <tgroup cols="5">
      <thead>
       <row>
	<entry>Function</entry>
	<entry>Returns</entry>
	<entry>Description</entry>
	<entry>Example</entry>
	<entry>Result</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry>trunc(macaddr)</entry>
	<entry>macaddr</entry>
	<entry>set last 3 bytes to zero</entry>
	<entry>trunc(macaddr '12:34:56:78:90:ab')</entry>
	<entry>12:34:56:00:00:00</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    The function <function>trunc</>(<type>macaddr</>) returns a MAC
    address with the last 3 bytes set to 0.  This can be used to
    associate the remaining prefix with a manufacturer.  The directory
    <filename>contrib/mac</> in the source distribution contains some
    utilities to create and maintain such an association table.
   </para>

  </sect1>

  <sect1 id="misc-functions">
   <title>Miscellaneous Functions</>

   <table>
    <title>Miscellaneous Functions</>
    <tgroup cols="3">
     <thead>
      <row><entry>Name</> <entry>Return type</> <entry>Description</></row>
     </thead>

     <tbody>
      <row>
       <entry>current_user</>
       <entry>name</>
       <entry>user name of current execution context</>
      </row>
      <row>
       <entry>user</>
       <entry>name</>
       <entry>equivalent to <function>current_user</></>
      </row>
      <row>
       <entry>session_user</>
       <entry>name</>
       <entry>session user name</>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    The <function>session_user</> is the user that initiated a database
    connection and is fixed for the duration of that connection. The
    <function>current_user</> is the user identifier that is applicable
    for permission checking. Currently it is always equal to the session
    user, but in the future there might be <quote>setuid</> functions and
    other facilities to allow the current user to change temporarily.
    In Unix parlance, the session user is the <quote>real user</>
    and the current user is the <quote>effective user</>.
   </para>

   <para>
    Note that these functions have special syntactic status in <acronym>SQL</>;
    they must be called without trailing parentheses.
   </para>

   <note>
    <title>Deprecated</>
    <para>
     The function <function>getpgusername()</> is an obsolete equivalent
     of <function>current_user</>.
    </para>
   </note>
  </sect1>

  <sect1 id="aggregate-functions">
   <title>Aggregate Functions</title>

   <note>
    <title>Author</title>
    <para>
     Written by <ulink url="mailto:isaac@azartmedia.com">Isaac Wilcox</ulink>
     on 2000-06-16.
    </para>
   </note>

   <para>
    <firstterm>Aggregate functions</firstterm> allow the generation of simple
    statistics about the values of given expressions over the selected set
    of rows.
<!--
	See also <xref linkend="sql" endterm="aggregates-tutorial"> and
    <xref linkend="syntax" endterm="aggregates-syntax">.
-->
	See also <xref linkend="syntax" endterm="aggregates-syntax">;
	refer to
	the <citetitle>PostgreSQL Tutorial</citetitle> for additional
	introductory information.
   </para>

   <para>
    <table tocentry="1">
     <title>Aggregate Functions</title>
     <tgroup cols="4">
      <thead>

       <row>
        <entry>Function</entry>
        <entry>Returns</entry>
        <entry>Description</entry>
        <entry>Example</entry>
        <entry>Notes</entry>
       </row>

      </thead>
      <tbody>
       <row>
        <entry>COUNT(*)</entry>
        <entry>int4</entry>
        <entry>Counts the selected rows.</entry>
        <entry>COUNT(*)</entry>
        <entry></entry>
       </row>

       <row>
        <entry>COUNT(<replaceable class="parameter">expression</replaceable>)</entry>
        <entry>int4</entry>
        <entry>Counts the selected rows for which the value of
	<replaceable class="parameter">expression</replaceable> is not
	NULL.</entry>
        <entry>COUNT(age)</entry>
        <entry></entry>
       </row>

       <row>
        <entry>SUM(<replaceable class="parameter">expression</replaceable>)</entry>
        <entry>Depends on the data type being summed.</entry>
        <entry>Finds the total obtained by adding the values of <replaceable class="parameter">expression</replaceable> across all selected rows.</entry>
        <entry>SUM(hours)</entry>
        <entry>Summation is supported on the following data types: int8, int4,
	int2, float4, float8, money, interval, numeric.  The result is numeric
	for any integer type, float8 for either float4 or float8 input,
	otherwise the same as the input data type.</entry>
       </row>

       <row>
        <entry>MAX(<replaceable class="parameter">expression</replaceable>)</entry>
        <entry>Same as the data type of the input expression.</entry>
        <entry>The maximum value of <replaceable class="parameter">expression</replaceable> across all selected rows.</entry>
        <entry>MAX(age)</entry>
        <entry>Finding the maximum value is supported on the following data types: int8, int4, int2, float4, float8, date, time, timetz, money, timestamp, interval, text, numeric.</entry>
       </row>

       <row>
        <entry>MIN(<replaceable class="parameter">expression</replaceable>)</entry>
        <entry>Same as the data type of the input expression.</entry>
        <entry>The minimum value of <replaceable class="parameter">expression</replaceable> across all selected rows.</entry>
        <entry>MIN(age)</entry>
        <entry>Finding the minimum value is supported on the following data types: int8, int4, int2, float4, float8, date, time, timetz, money, timestamp, interval, text, numeric.</entry>
       </row>

       <row>
        <entry>AVG(<replaceable class="parameter">expression</replaceable>)</entry>
        <entry>Depends on the data type being averaged.</entry>
        <entry>The average (mean) of the given values across all selected rows.</entry>
        <entry>AVG(age+1)</entry>
        <entry>Finding the mean value is supported on the following data
	types: int8, int4, int2, float4, float8, interval, numeric.  The
	result is numeric for any integer type, float8 for either float4 or
	float8 input, otherwise the same as the input data type.</entry>
       </row>

       <row>
        <entry>VARIANCE(<replaceable class="parameter">expression</replaceable>)</entry>
        <entry>Depends on the input data type.</entry>
        <entry>The sample variance of the given values.</entry>
        <entry>VARIANCE(reading)</entry>
        <entry>Finding the variance is supported on the following data
	types: int8, int4, int2, float4, float8, numeric.  The result is
	float8 for float4 or float8 input, otherwise numeric.</entry>
       </row>

       <row>
        <entry>STDDEV(<replaceable class="parameter">expression</replaceable>)</entry>
        <entry>Depends on the input data type.</entry>
        <entry>The sample standard deviation of the given values.</entry>
        <entry>STDDEV(reading)</entry>
        <entry>Finding the standard deviation is supported on the following
	data types: int8, int4, int2, float4, float8, numeric.  The result is
	float8 for float4 or float8 input, otherwise numeric.</entry>
       </row>

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

   <para>
    It should be noted that except for COUNT, these functions return NULL
    when no rows are selected.  In particular, SUM of no rows returns NULL,
    not zero as one might expect.
   </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:
-->