# User-defined functions

A function[1] in PostgreSQL is a reusable piece of code that executes when called. It can accept arguments and return outputs.

Functions were originally introduced in PostgreSQL 9.5, and you can use a variety of programming languages to write them in. Functions can do anything that you could do in PostgreSQL, except handle transactions. That means they can't commit or rollback.

When you write functions using SQL[2] you can still update and insert data in a function, but you can't group multiple queries into a transaction. SQL functions always return the result of the last query in the function.

Functions are normally applied to columns when you perform a SELECT on one of your tables. You can also use them on their own to execute one or more queries together!

For example, let's say you have this table[3] of users in your mailing list. The last_opened column is the number of seconds since they last opened one of your e-mails:

name TEXT email TEXT last_opened INTEGER
Bob Smith [email protected] 65615
Rolf Smith [email protected] 8587597
Susan Williams [email protected] 56
Anne Pun [email protected] 2419200‬

Now let's say you want to delete users that haven't opened one of your e-mails in a week:

DELETE FROM users WHERE last_opened > 604800;

This is all well and good, but when you want to do this again (next week), you'll have to write the query again. Who knows, you might type the number wrong and mistakenly delete a bunch of good users!

Let's create a function for this:

CREATE FUNCTION delete_inactive() RETURNS void AS $$
    DELETE FROM users WHERE last_opened > 604800;

A few things going on here:

  • CREATE FUNCTION delete_inactive() tells PostgreSQL that we want to create a function called delete_inactive. The brackets are empty, so there are no parameters.
  • RETURNS void tells PostgreSQL this function won't return anything.
  • AS $$ starts the function body.
  • Queries go next, separated by semicolons. Here we've got only one, but you could have more.
  • $$ LANGUAGE SQL; terminates the queries (with the $$) and also specifies which language[4] we used to write the function. SQL allows us to write normal queries.

To execute the function:

SELECT delete_inactive();

Here's where the RETURNS void comes into play. That SELECT statement won't give you a value back.

# Function arguments and return values: base types

Often we want our functions to return values so that we can use them in our queries when we call the function.

Let's modify the function above so it deletes users after an arbitrary amount of time, determined by an argument:

CREATE FUNCTION delete_inactive(seconds NUMERIC) RETURNS void AS $$
    DELETE FROM users WHERE last_opened > seconds;

We've added seconds NUMERIC inside the brackets. That tells PostgreSQL that when we call the function, we will pass a single numeric value.

Inside the function, that value is known as seconds. It is used in the WHERE clause.

Calling the function requires now the argument. Below, deleting members who didn't open for a week and a day respectively:

SELECT delete_inactive(604800);
SELECT delete_inactive(86400);

When defining the function, we can add more parameters if our function needs them (e.g. (seconds NUMERIC, max_deletion NUMERIC)). If we do that, when we call the function we need to pass two arguments.


Now we have two functions: delete_inactive() and delete_inactive(numeric). Be careful, and make sure to delete functions if you don't need them anymore!

Function names are therefore not unique. Function names and their arguments are unique. That means you can reuse a function name as long as the arguments are different.

# Returning values from a function

Now let's make the function return how many users it deleted. First we must change RETURNS void to RETURNS BIGINT.

In order to change the return type of a function though, we must first delete it manually:

DROP FUNCTION delete_inactive(numeric);

And then re-create it:

    DELETE FROM users WHERE last_opened > seconds;

Note the RETURNS BIGINT. We'll be using the COUNT function to see how many users were deleted, and COUNT returns a BIGINT.

Now this function is invalid because we've said it returns a number, but no number is currently returned. Let's add a SELECT statement to ensure that happens:

    WITH deleted AS (
        DELETE FROM users WHERE last_opened > seconds RETURNING *
    SELECT COUNT(*) FROM deleted;

Note the OR REPLACE can be used to re-create a function that already exists, but it can't be used to change the return type of a function.

Now running the function:

SELECT delete_inactive(300);

Gives us back the number of users deleted!


There are more types of parameters[5] and return values, which work in similar ways to these. Check out the references below!

  1. CREATE FUNCTION (PostgreSQL Official Documentation) (opens new window) ↩︎

  2. Query Language (SQL) Functions (PostgreSQL Official Documentation) (opens new window) ↩︎

  3. Interactive database for this lecture (opens new window) ↩︎

  4. 37.3. User-Defined Functions (PostgreSQL Official Documentation) (opens new window) ↩︎

  5. PL/pgSQL Function Parameters (PostgreSQL Tutorial) (opens new window) ↩︎