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

1
2
3
4
5
6
create or replace function hello_world() -- 1returns text -- 2language sql -- 3as $$  -- 4  select 'hello world';  -- 5$$; --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.

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

1
2
3
4
5
| id  | name     || --- | -------- || 1   | Tatooine || 2   | Alderaan || 3   | Kashyyyk |

1
2
3
4
5
6
| id  | name             | planet_id || --- | ---------------- | --------- || 1   | Anakin Skywalker | 1         || 2   | Luke Skywalker   | 1         || 3   | Princess Leia    | 2         || 4   | Chewbacca        | 3         |

We could create a function which returns all the planets:

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

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

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

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

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

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

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

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. There are two main ways to restrict this:

1. On a case-by-case basis. Specifically revoke permissions for functions you want to protect. Execution needs to be revoked for both public and the role you're restricting:

   1
   2
   revoke execute on function public.hello_world from public;revoke execute on function public.hello_world from anon;

2. Restrict function execution by default. Specifically grant access when you want a function to be executable by a specific role.

   To restrict all existing functions, revoke execution permissions from both public and the role you want to restrict:

   1
   2
   revoke execute on all functions in schema public from public;revoke execute on all functions in schema public from anon, authenticated;

   To restrict all new functions, change the default privileges for both public and the role you want to restrict:

   1
   2
   alter default privileges in schema public revoke execute on functions from public;alter default privileges in schema public revoke execute on functions from anon, authenticated;

   You can then regrant permissions for a specific function to a specific role:

   1
   grant execute on function public.hello_world to authenticated;

Debugging functions#

You can add logs to help you debug functions. This is especially recommended for complex functions.

Good targets to log include:

• Values of (non-sensitive) variables
• Returned results from queries

General logging#

To create custom logs in the Dashboard's Postgres Logs, you can use the raise keyword. By default, there are 3 observed severity levels:

• log
• warning
• exception (error level)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create function logging_example(  log_message text,  warning_message text,  error_message text)returns voidlanguage plpgsqlas $$begin  raise log 'logging message: %', log_message;  raise warning 'logging warning: %', warning_message;  -- immediately ends function and reverts transaction  raise exception 'logging error: %', error_message;end;$$;select logging_example('LOGGED MESSAGE', 'WARNING MESSAGE', 'ERROR MESSAGE');

Error handling#

You can create custom errors with the raise exception keywords.

A common pattern is to throw an error when a variable doesn't meet a condition:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace function error_if_null(some_val text)returns textlanguage plpgsqlas $$begin  -- error if some_val is null  if some_val is null then    raise exception 'some_val should not be NULL';  end if;  -- return some_val if it is not null  return some_val;end;$$;select error_if_null(null);

Value checking is common, so Postgres provides a shorthand: the assert keyword. It uses the following format:

1
2
-- throw error when condition is falseassert <some condition>, 'message';

Below is an example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create function assert_example(name text)returns uuidlanguage plpgsqlas $$declare  student_id uuid;begin  -- save a user's id into the user_id variable  select    id into student_id  from attendance_table  where student = name;  -- throw an error if the student_id is null  assert student_id is not null, 'assert_example() ERROR: student not found';  -- otherwise, return the user's id  return student_id;end;$$;select assert_example('Harry Potter');

Error messages can also be captured and modified with the exception keyword:

1
2
3
4
5
6
7
8
9
10
11
12
13
create function error_example()returns voidlanguage plpgsqlas $$begin  -- fails: cannot read from nonexistent table  select * from table_that_does_not_exist;  exception      when others then          raise exception 'An error occurred in function <function name>: %', sqlerrm;end;$$;

Advanced logging#

For more complex functions or complicated debugging, try logging:

• Formatted variables
• Individual rows
• Start and end of function calls

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
create or replace function advanced_example(num int default 10)returns textlanguage plpgsqlas $$declare    var1 int := 20;    var2 text;begin    -- Logging start of function    raise log 'logging start of function call: (%)', (select now());    -- Logging a variable from a SELECT query    select      col_1 into var1    from some_table    limit 1;    raise log 'logging a variable (%)', var1;    -- It is also possible to avoid using variables, by returning the values of your query to the log    raise log 'logging a query with a single return value(%)', (select col_1 from some_table limit 1);    -- If necessary, you can even log an entire row as JSON    raise log 'logging an entire row as JSON (%)', (select to_jsonb(some_table.*) from some_table limit 1);    -- When using INSERT or UPDATE, the new value(s) can be returned    -- into a variable.    -- When using DELETE, the deleted value(s) can be returned.    -- All three operations use "RETURNING value(s) INTO variable(s)" syntax    insert into some_table (col_2)    values ('new val')    returning col_2 into var2;    raise log 'logging a value from an INSERT (%)', var2;    return var1 || ',' || var2;exception    -- Handle exceptions here if needed    when others then        raise exception 'An error occurred in function <advanced_example>: %', sqlerrm;end;$$;select advanced_example();

Resources#

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

Deep dive#

Create Database Functions#

Call Database Functions using JavaScript#

Using Database Functions to call an external API#