Database

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
1Tatooine
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 = ''
_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. 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:


    _10
    revoke execute on function public.hello_world from public;
    _10
    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:


    _10
    revoke execute on all functions in schema public from public;
    _10
    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:


    _10
    alter default privileges in schema public revoke execute on functions from public;
    _10
    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:


    _10
    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)

_18
create function logging_example(
_18
log_message text,
_18
warning_message text,
_18
error_message text
_18
)
_18
returns void
_18
language plpgsql
_18
as $$
_18
begin
_18
raise log 'logging message: %', log_message;
_18
raise warning 'logging warning: %', warning_message;
_18
_18
-- immediately ends function and reverts transaction
_18
raise exception 'logging error: %', error_message;
_18
end;
_18
$$;
_18
_18
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:


_15
create or replace function error_if_null(some_val text)
_15
returns text
_15
language plpgsql
_15
as $$
_15
begin
_15
-- error if some_val is null
_15
if some_val is null then
_15
raise exception 'some_val should not be NULL';
_15
end if;
_15
-- return some_val if it is not null
_15
return some_val;
_15
end;
_15
$$;
_15
_15
select error_if_null(null);

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


_10
-- throw error when condition is false
_10
assert <some condition>, 'message';

Below is an example


_22
create function assert_example(name text)
_22
returns uuid
_22
language plpgsql
_22
as $$
_22
declare
_22
student_id uuid;
_22
begin
_22
-- save a user's id into the user_id variable
_22
select
_22
id into student_id
_22
from attendance_table
_22
where student = name;
_22
_22
-- throw an error if the student_id is null
_22
assert student_id is not null, 'assert_example() ERROR: student not found';
_22
_22
-- otherwise, return the user's id
_22
return student_id;
_22
end;
_22
$$;
_22
_22
select assert_example('Harry Potter');

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


_13
create function error_example()
_13
returns void
_13
language plpgsql
_13
as $$
_13
begin
_13
-- fails: cannot read from nonexistent table
_13
select * from table_that_does_not_exist;
_13
_13
exception
_13
when others then
_13
raise exception 'An error occurred in function <function name>: %', sqlerrm;
_13
end;
_13
$$;

Advanced logging

For more complex functions or complicated debugging, try logging:

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

_43
create or replace function advanced_example(num int default 10)
_43
returns text
_43
language plpgsql
_43
as $$
_43
declare
_43
var1 int := 20;
_43
var2 text;
_43
begin
_43
-- Logging start of function
_43
raise log 'logging start of function call: (%)', (select now());
_43
_43
-- Logging a variable from a SELECT query
_43
select
_43
col_1 into var1
_43
from some_table
_43
limit 1;
_43
raise log 'logging a variable (%)', var1;
_43
_43
-- It is also possible to avoid using variables, by returning the values of your query to the log
_43
raise log 'logging a query with a single return value(%)', (select col_1 from some_table limit 1);
_43
_43
-- If necessary, you can even log an entire row as JSON
_43
raise log 'logging an entire row as JSON (%)', (select to_jsonb(some_table.*) from some_table limit 1);
_43
_43
-- When using INSERT or UPDATE, the new value(s) can be returned
_43
-- into a variable.
_43
-- When using DELETE, the deleted value(s) can be returned.
_43
-- All three operations use "RETURNING value(s) INTO variable(s)" syntax
_43
insert into some_table (col_2)
_43
values ('new val')
_43
returning col_2 into var2;
_43
_43
raise log 'logging a value from an INSERT (%)', var2;
_43
_43
return var1 || ',' || var2;
_43
exception
_43
-- Handle exceptions here if needed
_43
when others then
_43
raise exception 'An error occurred in function <advanced_example>: %', sqlerrm;
_43
end;
_43
$$;
_43
_43
select advanced_example();

Resources

Deep dive

Create Database Functions

Call Database Functions using JavaScript

Using Database Functions to call an external API