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.
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:
- PLPGSQL — SQL Procedural Language
- PLTCL — Tcl Procedural Language
- PLPERL — Perl Procedural Language
- PLPYTHON — Python Procedural Language
- External PL – Other PL outside PostgreSQL distribution
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:
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:
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!
