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".

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

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.

1select 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:

We could create a function which returns all the planets:

1create or replace function get_planets()
2returns setof planets
3language sql
4as $$
5  select * from planets;
6$$;

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

1select *
2from get_planets()
3where 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.

1create or replace function add_planet(name text)
2returns bigint
3language plpgsql
4as $$
5declare
6  new_row bigint;
7begin
8  insert into planets(name)
9  values (add_planet.name)
10  returning id into new_row;
11
12  return new_row;
13end;
14$$;

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

1select * 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:

1create function hello_world()
2returns text
3language plpgsql
4security definer set search_path = public
5as $$
6begin
7  select 'hello world';
8end;
9$$;

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.

1ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
2
3-- Choose which roles can execute functions
4GRANT EXECUTE ON FUNCTION hello_world TO authenticated;
5GRANT EXECUTE ON FUNCTION hello_world TO service_role;

Resources#

• Official Client libraries: JavaScript and Flutter
• Community client libraries: github.com/supabase-community
• PostgreSQL Official Docs: Chapter 9. Functions and Operators
• PostgreSQL Reference: CREATE FUNCTION

Deep Dive#

Create Database Functions#

Call Database Functions using JavaScript#

Using Database Functions to call an external API#