# User-defined functions
A function 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 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 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; $$ 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 voidtells 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 we used to write the function.
SQLallows us to write normal queries.
To execute the function:
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;
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
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(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
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;
RETURNS BIGINT. We'll be using the
COUNT function to see how many users were deleted, and
COUNT returns a
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;
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:
Gives us back the number of users deleted!
There are more types of parameters and return values, which work in similar ways to these. Check out the references below!