PostgreSQL Functions

PostgreSQL is a powerful and easy-to-use database management system that provides secure, fast, and very intuitive features, allowing you to focus on the data and not the query syntax.

One way PostgreSQL makes its usage very easy is the functionality of pre-defined functions. These functions allow you to call them and pass the required arguments to get an output.

In this post, we will give you a cheat sheet of the most common PostgreSQL functions and even show you how to define custom ones if needed.

Mathematical Functions

The first category of PostgreSQL functions is mathematical functions. These allows you to perform mathematical operations to the passed arguments.

Function	Functionality
abs(x)	Returns an absolute value of x
pi()	Returns the pi constant value
sqrt(x)	Returns a square root of x
mod(x,y)	Returns the modulo value of x and y
trunc(x)	Truncates x to towards 0
exp(x)	Returns exponential value of x
random()	Returns random values between 0 and 1.0
pow(a, b)	Raise a to the power of b
factorial(x)	Returns factorial value of x
ln()	Natural logarithim

These are some popular mathematical functions you can use in PostgreSQL queries.

Aggregate Functions

Another set of standard PostgreSQL functions are aggregate functions. These types of functions perform an action on a set of values and return a single result.

Popular PostgreSQL aggregate functions include:

Function	Functionality
avg()	Return the average mean for a set of values
min()	Minimum value for a given set of values.
max()	Maximum value for a given set of values
sum()	Sum of the input values
count()	Number of rows for non-null values
*count()**	Returns number of input rows

String Functions

In PostgreSQL, String functions are responsible for manipulating string values and return a value.

Common string functions include:

Function	Functionality
lower()	Converts the input string to lowercase
upper()	Converts the input string to uppercase
char_length()	Returns the number of characters in a string
\|\|	Concatenates the string on the left side with the string on the right side
bit_length()	Number of bits in a string
reverse()	Reverse the input string
repeat()	Repeat the passed string the specified number of times.

Date and Time Functions

As the name suggests, the time and date functions work with date and time data.

Common functions include:

Function	Functionality
now()	Returns current date and time
current_time()	Current time of the day
current_date()	Returns current date
age()	Returns the symbolic result in the form of year, month, and day from the current date
extract()	Extract subfield from a timestamp

Geometric Functions

Geometric functions perform a geometric operation on the passed set of values. Although you might not find yourself using these functions as often, they do come in handy.

Common examples of such functions include:

Function	Functionality
area	Returns the area of the object.
diameter	Returns the diameter of a circle
length	Returns the length of a path
box(circle)	Convert a circle to box
box(point)	Convert point to an empty box
path(polygon)	Convert polygon to path
polygon(path)	Convert path to polygon

Network Address Functions

Network functions are useful when working with inet and cidr types. Common functions include:

Function	Functionality
host(inet)	Returns IP address as text
masklen(inet)	Returns the netmask length
network(inet)	Returns the network portion of an address
family(inet)	Extract the family of address
netmask()	Returns network mask for address
inet_same_family(inet, inet)	Returns true if both inet addresses are same family

The above functions are a fraction of the functions supported by PostgreSQL.

How to Create PostgreSQL User Functions

There are some instances where you need to create a custom function. PostgreSQL allows you to create such functions with names, parameters, and return types.

To create a function in PostgreSQL, use the CREATE FUNCTION statement.

The general syntax for creating a custom function is as shown.

CREATE FUNCTION function_name(paramaters)
RETURNS data_type;
LANGUAGE procedural_language_type;
AS
$$
DECLARE
-- declare your variables here
BEGIN
-- code what the function does
END
$$

Let us break down the above syntax step by step.

We start by using the CREATE FUNCTION statements followed by the function name. The function name can be anything meaningful.
Next, we pass the function parameters inside a pair of parentheses. The number of parameters can be anything from 0 to infinity.
Inside the function block (indicated by the indentation although not required), we use the RETURN keyword followed by the data type which the function returns.
The next part defines the Procedural Language of the function, the most common of which is the PLPGSQL. Other PostgreSQL procedural functions include PLTCL, PLPERL, PLPYTHON.
Finally, we use the double dollar sign symbol to initialize the function. This is where we declare variables and the function logic placed inside the begin and end blocks.

You can learn more about the PostgreSQL Procedural Languages from the links provided below:

HINT: Custom functions in PostgreSQL are closely similar to procedures. However, unlike procedures, functions return a value.

Example Custom Function

Let us illustrate a simple function that fetches the number of items that match a simple criterion.

Let us take the sakila database, for example. We can get the number of films whose rental value is between 2 and 4 dollars.

A simple function is:

CREATE FUNCTION get_films(x int, y int)
RETURNS int
LANGUAGE PLPGSQL
AS
$$
DECLARE
num_films integer;
BEGIN
SELECT count(*)
INTO num_films
FROM film
WHERE rental_rate BETWEEN x AND y;
RETURN num_films;
END;
$$

Once we declare the function, we can call it and pass the values as:

SELECT get_films(2, 4) AS number_of_films;

Once we run the query above, we should get the number of films that match the criteria above.

In most cases, you will not need to define such functions because there are other simpler built-in methods to accomplish such a task. That being the case, the function above illustrates how to work with functions.

Conclusion

In this tutorial, we illustrated common PostgreSQL functions to help perform tasks more efficiently. Finally, we showed you how to create and use functions for custom requirements.

Thank you for reading and happy experimenting!