Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Learn more
Home

Database Functions

Postgres has built-in support for SQL functions. These functions live inside your database, and they can be used with the API.

Quick demo

Getting started

Supabase provides several options for creating database functions. You can use the Dashboard or create them directly using SQL. We provide a SQL editor within the Dashboard, or you can connect to your database and run the SQL queries yourself.

  1. Go to the "SQL editor" section.
  2. Click "New Query".
  3. Enter the SQL to create or replace your Database function.
  4. Click "Run" or cmd+enter (ctrl+enter).

Simple functions

Let's create a basic Database Function which returns a string "hello world".


_10
create or replace function hello_world() -- 1
_10
returns text -- 2
_10
language sql -- 3
_10
as $$ -- 4
_10
select 'hello world'; -- 5
_10
$$; --6

Show/Hide Details

At it's most basic a function has the following parts:

  1. create or replace function hello_world(): The function declaration, where hello_world is the name of the function. You can use either create when creating a new function or replace when replacing an existing function. Or you can use create or replace together to handle either.
  2. returns text: The type of data that the function returns. If it returns nothing, you can returns void.
  3. language sql: The language used inside the function body. This can also be a procedural language: plpgsql, plv8, plpython, etc.
  4. as $$: The function wrapper. Anything enclosed inside the $$ symbols will be part of the function body.
  5. select 'hello world';: A simple function body. The final select statement inside a function body will be returned if there are no statements following it.
  6. $$;: The closing symbols of the function wrapper.

After the Function is created, we have several ways of "executing" the function - either directly inside the database using SQL, or with one of the client libraries.


_10
select hello_world();

Returning data sets

Database Functions can also return data sets from Tables or Views.

For example, if we had a database with some Star Wars data inside:

Planets

idname
1Tattoine
2Alderaan
3Kashyyyk

People

idnameplanet_id
1Anakin Skywalker1
2Luke Skywalker1
3Princess Leia2
4Chewbacca3

We could create a function which returns all the planets:


_10
create or replace function get_planets()
_10
returns setof planets
_10
language sql
_10
as $$
_10
select * from planets;
_10
$$;

Because this function returns a table set, we can also apply filters and selectors. For example, if we only wanted the first planet:


_10
select *
_10
from get_planets()
_10
where id = 1;

Passing parameters

Let's create a Function to insert a new planet into the planets table and return the new ID. Note that this time we're using the plpgsql language.


_14
create or replace function add_planet(name text)
_14
returns bigint
_14
language plpgsql
_14
as $$
_14
declare
_14
new_row bigint;
_14
begin
_14
insert into planets(name)
_14
values (add_planet.name)
_14
returning id into new_row;
_14
_14
return new_row;
_14
end;
_14
$$;

Once again, you can execute this function either inside your database using a select query, or with the client libraries:


_10
select * from add_planet('Jakku');

Suggestions

Database Functions vs Edge Functions

For data-intensive operations, use Database Functions, which are executed within your database and can be called remotely using the REST and GraphQL API.

For use-cases which require low-latency, use Edge Functions, which are globally-distributed and can be written in Typescript.

Security definer vs invoker

Postgres allows you to specify whether you want the function to be executed as the user calling the function (invoker), or as the creator of the function (definer). For example:


_10
create function hello_world()
_10
returns text
_10
language plpgsql
_10
security definer set search_path = public
_10
as $$
_10
begin
_10
select 'hello world';
_10
end;
_10
$$;

It is best practice to use security invoker (which is also the default). If you ever use security definer, you must set the search_path. This limits the potential damage if you allow access to schemas which the user executing the function should not have.

Function privileges

By default, database functions can be executed by any role. You can restrict this by altering the default privileges and then choosing which roles can execute functions.


_10
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
_10
_10
-- Choose which roles can execute functions
_10
GRANT EXECUTE ON FUNCTION hello_world TO authenticated;
_10
GRANT EXECUTE ON FUNCTION hello_world TO service_role;

Resources

Deep dive

Create Database Functions

Call Database Functions using JavaScript

Using Database Functions to call an external API