path: root/doc/src/sgml/func.sgml

<Chapter 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>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>
<title>String Functions</title>

<Para>
SQL92 defines string functions with specific syntax. Some of these
are implemented using other <ProductName>Postgres</ProductName> functions.
</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> position(text in text) </ENTRY>
<ENTRY> int4 </ENTRY>
<ENTRY> location of specified substring </ENTRY>
<ENTRY> position('o' in 'Tom') </ENTRY>
  </ROW>
  <ROW>
<ENTRY> substring(text [from int] [for int]) </ENTRY>
<ENTRY> text </ENTRY>
<ENTRY> extract specified substring </ENTRY>
<ENTRY> substring('Tom' from 2 for 2) </ENTRY>
  </ROW>
  <ROW>
<ENTRY> trim([leading|trailing|both] [text] from text) </ENTRY>
<ENTRY> text </ENTRY>
<ENTRY> trim characters from text </ENTRY>
<ENTRY> trim(both 'x' from 'xTomx') </ENTRY>
  </ROW>
</TBODY>
</TGROUP>
</TABLE>
</Para>

<Para>
Many 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> lower(text) </ENTRY>
<ENTRY> text </ENTRY>
<ENTRY> convert text to lower case </ENTRY>
<ENTRY> lower('TOM') </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> position(text,text) </ENTRY>
<ENTRY> text </ENTRY>
<ENTRY> extract 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>
  <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>
Most functions explicitly defined for text will work for char() and varchar() arguments.
</para>

<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> span preserving 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> specified portion of date field </ENTRY>
<ENTRY> date_part('dow','now'::datetime) </ENTRY>
  </ROW>
  <ROW>
<ENTRY> date_part(text,timespan) </ENTRY>
<ENTRY> float8 </ENTRY>
<ENTRY> specified portion of time field </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 at specified units </ENTRY>
<ENTRY> date_trunc('month','now'::abstime) </ENTRY>
  </ROW>
  <ROW>
<ENTRY> isfinite(abstime) </ENTRY>
<ENTRY> bool </ENTRY>
<ENTRY> TRUE if this is a finite time </ENTRY>
<ENTRY> isfinite('now'::abstime) </ENTRY>
  </ROW>
  <ROW>
<ENTRY> isfinite(datetime) </ENTRY>
<ENTRY> bool </ENTRY>
<ENTRY> TRUE if this is a finite time </ENTRY>
<ENTRY> isfinite('now'::datetime) </ENTRY>
  </ROW>
  <ROW>
<ENTRY> isfinite(timespan) </ENTRY>
<ENTRY> bool </ENTRY>
<ENTRY> TRUE if this is 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>
<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> TRUE if this is a closed path </ENTRY>
<ENTRY> isclosed('((0,0),(1,1),(2,0))'::path) </ENTRY>
  </ROW>
  <ROW>
<ENTRY> isopen(path) </ENTRY>
<ENTRY> bool </ENTRY>
<ENTRY> TRUE if this is 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 variant </ENTRY>
<ENTRY> popen('[(0,0),(1,1),(2,0)]'::path) </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> points(path) </ENTRY>
<ENTRY> int4 </ENTRY>
<ENTRY> number of points in path </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 variant </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 of box </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 polygon with 12 points </ENTRY>
<ENTRY> polygon('((0,0),2.0)'::circle) </ENTRY>
  </ROW>
  <ROW>
<ENTRY> polygon(npts,circle) </ENTRY>
<ENTRY> polygon </ENTRY>
<ENTRY> convert to polygon with npts points </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>

</chapter>