# 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	bobsmith@example.com	65615
Rolf Smith	rolfsmith@example.com	8587597
Susan Williams	susanwilliams@example.com	56
Anne Pun	annepun@example.com	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;
$$ LANGUAGE SQL;

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;
$$ LANGUAGE SQL;

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.

WARNING

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:

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

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:

CREATE OR REPLACE FUNCTION delete_inactive(seconds NUMERIC) RETURNS BIGINT AS $$
    WITH deleted AS (
        DELETE FROM users WHERE last_opened > seconds RETURNING *
    )
    SELECT COUNT(*) FROM deleted;
$$ LANGUAGE SQL;

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!

delete_inactive
3

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

← Composite primary keys Composite types and sets in functions →