Support Functions

 Functions enable you to access specialized routines from SQL. They take one or more arguments and return a result.

 Suppose you want to uppercase a value or column. No command will perform this operation, but a function can handle it. POSTGRESQL has a function called upper that takes a single string argument and returns the argument in uppercase. The function call upper(col) calls the function upper with col as its argument and returns it in uppercase. Figure  shows an example of the use of the upper function.   

        test=> SELECT * FROM functest;
         name 
        ------
         Judy
        (1 row) 
         
        test=> SELECT upper(name) FROM functest;
         upper 
        -------
         JUDY
        (1 row)  

POSTGRESQL provides many functions. Table  shows the most common ones, organized by the data types supported.

 

Table: Common functions

Type	Function	Example	Returns
Character	length ()	length(col)	length of col
String	character_length ()	character_length(col)	length of col, same as length()
	octet_length ()	octet_length(col)	length of col, including multibyte overhead
	trim ()	trim(col)	col with leading and trailing spaces removed
	trim(BOTH...)	trim(BOTH col)	same as trim()
	trim(LEADING...)	trim(LEADING col)	col with leading spaces removed
	trim(TRAILING...)	trim(TRAILING col)	col with trailing spaces removed
	trim(...FROM...)	trim(str FROM col)	col with leading and trailing str removed
	rpad ()	rpad(col, len)	col padded on the right to len characters
	rpad()	rpad(col, len, str)	col padded on the right using str
	lpad ()	lpad(col, len)	col padded on the left to len characters
	lpad()	lpad(col, len, str)	col padded on the left using str
	upper ()	upper(col)	col uppercased
	lower ()	lower(col)	col lowercased
	initcap ()	initcap(col)	col with the first letter capitalized
	strpos ()	strpos(col, str)	position of str in col
	position ()	position(str IN col)	same as strpos()
	substr ()	substr(col, pos)	col starting at position pos
	substring (...FROM...)	substring(col FROM pos)	same as substr()
	substr()	substr(col, pos, len)	col starting at position pos for length len
	substring(...FROM...	substring(col FROM pos	same as substr()
	FOR...)	FOR len)
	translate ()	translate(col, from, to)	col with from characters mapped to to
	to_number ()	to_number(col, mask)	convert col to NUMERIC() based on mask
	to_date ()	to_date(col, mask)	convert col to DATE based on mask
	to_timestamp ()	to_timestamp(col, mask)	convert col to TIMESTAMP based on mask
Number	round ()	round(col)	round to an integer
	round()	round(col, len)	NUMERIC() col rounded to len decimal places
	trunc ()	trunc(col)	truncate to an integer
	trunc()	trunc(col, len)	NUMERIC() col truncated to len decimal places
	abs ()	abs(col)	absolute value
	factorial ()	factorial(col)	factorial
	sqrt ()	sqrt(col)	square root
	cbrt ()	cbrt(col)	cube root
	exp ()	exp(col)	exponential
	ln ()	ln(col)	natural logarithm
	log ()	log(log)	base-10 logarithm
	to_char ()	to_char(col, mask)	convert col to a string based on mask
Temporal	date_part ()	date_part(units, col)	units part of col
	extract (...FROM...)	extract(units FROM col)	same as date_part()
	date_trunc ()	date_trunc(units, col )	col rounded to units
	isfinite ()	isfinite(col)	BOOLEAN indicating whether col is a valid date
	now ()	now()	TIMESTAMP representing current date and time
	timeofday ()	timeofday()	string showing date/time in Unix format
	overlaps ()	overlaps(c1, c2, c3, c4)	BOOLEAN indicating whether col's overlap in time
	to_char ()	to_char(col, mask)	convert col to string based on mask
Geometric			see psql's \df for a list of geometric functions
Network	broadcast ()	broadcast(col)	broadcast address of col
	host ()	host(col)	host address of col
	netmask ()	netmask(col)	netmask of col
	masklen ()	masklen(col)	mask length of col
	network ()	network(col)	network address of col
NULL	nullif ()	nullif(col1, col2)	return NULL if col1 equals col2, else return col1
	coalesce ()	coalesce(col1, col2,...)	return first non-NULL argument

Psql's \df shows all defined functions and their arguments. Section  describes all the psql commands.

 If you call a function with a type for which it is not defined, you will get an error message, as shown in the first query of Figure .  

        test=> SELECT date_part('year', '5/8/1971');
        ERROR:  Function 'date_part(unknown, unknown)' does not exist
                Unable to identify a function that satisfies the given argument types
                You may need to add explicit typecasts
        test=> SELECT date_part('year', CAST('5/8/1971' AS DATE));
         date_part 
        -----------
              1971
        (1 row)  

In the first query, 5/8/1971 is a character string, not a date. The second query converts 5/8/1971 to a date, so date_part()  can be used.