Chapter 10. Functions

Many data types have functions available for conversion to other related types. In addition, there are some type-specific functions. Functions which are also available through operators are documented as operators only.

Some functions defined for text are also available for char() and varchar().

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

Table 10-1. Mathematical Functions

float(int) float8 convert integer to floating point float(2)
float4(int) float4 convert integer to floating point float4(2)
int integer(float) convert floating point to integer integer(2.0)

Many of the string functions are available for text, varchar(), and char() types. At the moment, some functions are available only for the text type.

Table 10-2. String Functions

lower(text) text convert text to lower case lower('TOM')
lpad(text,int,text) text left pad string to specified length lpad('hi',4,'??')
ltrim(text,text) text left trim characters from text ltrim('xxxxtrim','x')
position(text,text) text extract specified substring position('high','ig')
rpad(text,int,text) text right pad string to specified length rpad('hi',4,'x')
rtrim(text,text) text right trim characters from text rtrim('trimxxxx','x')
substr(text,int[,int]) text extract specified substring substr('hi there',3,5)
upper(text) text convert text to upper case upper('tom')

Table 10-3. Date/Time Functions

isfinite(abstime) bool TRUE if this is a finite time isfinite('now'::abstime)
datetime(abstime) datetime convert to datetime datetime('now'::abstime)
datetime(date) datetime convert to datetime datetime('today'::date)
datetime(date,time) datetime convert to datetime datetime('1998-02-24':datetime, '23:07'::time);
age(datetime,datetime) timespan span preserving months and years age('now','1957-06-13':datetime)
date_part(text,datetime) float8 specified portion of date field date_part('dow','now'::datetime)
date_trunc(text,datetime) datetime truncate date at specified units date_trunc('month','now'::abstime)
isfinite(datetime) bool TRUE if this is a finite time isfinite('now'::datetime)
abstime(datetime) abstime convert to abstime abstime('now'::datetime)
timespan(reltime) timespan convert to timespan timespan('4 hours'::reltime)
datetime(date,time) datetime convert to datetime datetime('1998-02-25'::date,'06:41'::time)
date_part(text,timespan) float8 specified portion of time field date_part('hour','4 hrs 3 mins'::timespan)
isfinite(timespan) bool TRUE if this is a finite time isfinite('4 hrs'::timespan)
reltime(timespan) reltime convert to reltime reltime('4 hrs'::timespan)

Table 10-4. Geometric Functions

box(point,point) box convert points to box box('(0,0)'::point,'(1,1)'::point)
area(box) float8 area of box area('((0,0),(1,1))'::box)
isopen(path) bool TRUE if this is an open path isopen('[(0,0),(1,1),(2,0)]'::path)
isclosed(path) bool TRUE if this is a closed path isclosed('((0,0),(1,1),(2,0))'::path)
circle(point,float8) circle convert to circle circle('(0,0)'::point,2.0)
polygon(npts,circle) polygon convert to polygon with npts points polygon(12,'((0,0),2.0)'::circle)
center(circle) float8 center of object center('((0,0),2.0)'::circle)
radius(circle) float8 radius of circle radius('((0,0),2.0)'::circle)
diameter(circle) float8 diameter of circle diameter('((0,0),2.0)'::circle)
area(circle) float8 area of circle area('((0,0),2.0)'::circle)

SQL92 defines functions with specific syntax. Some of these are implemented using other Postgres functions.

Table 10-5. SQL92 Text Functions

position(text in text) int4 extract specified substring position('o' in 'Tom')
substring(text [from int] [for int]) text extract specified substring substring('Tom' from 2 for 2)
trim([leading|trailing|both] [text] from text) text trim characters from text trim(both 'x' from 'xTomx')