Database Functions

Postgres has built-in support for SQL functions. These functions live inside your database, and you can call them from your app with rpc().

Quick demo#

Getting started#

Create a database function from the Dashboard, or write the SQL yourself against a direct connection.

To use the Dashboard:

1. Go to the SQL Editor section.
2. Click New Query.
3. Enter the SQL that creates or replaces your database function.
4. Click Run. You can also press cmd+enter or ctrl+enter.

Basic functions #

Create a basic database function that returns the string "hello world".

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

After you create the function, you can run it inside the database with SQL, or with one of the client libraries.

1
select hello_world();

Returning data sets#

A database function can also return a data set from a table or a view.

For example, take a database holding some Star Wars data:

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

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

The following function returns all the planets:

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

Because this function returns a table set, you can apply filters and selectors to it. To get the first planet only:

1
select *
2
from get_planets()
3
where id = 1;

Passing parameters#

Create a function that inserts a new planet into the planets table and returns the new ID. This function uses the plpgsql language.

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

You can run this function inside your database with 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. They run inside your database, and you can call them remotely with the REST and GraphQL API.

For use cases that need low latency, use Edge Functions. They're globally distributed and you write them in TypeScript.

Security definer vs invoker#

Postgres runs a function either as the user calling it (invoker) or as its creator (definer). For example:

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

Prefer security invoker, which is also the default. When you use security definer, you must set the search_path.

With an empty search path, search_path = '', name the schema for every relation in the function body, such as from public.table. An empty search path limits the damage when the function can reach a schema you don't want the calling user to reach.

Function privileges#

By default, any role can run a database function. You can restrict execution in two ways:

1. Revoke on a case-by-case basis. Revoke execute for the functions you want to protect, from both public and the role you're restricting:

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

2. Restrict execution by default, then grant access to the roles that need each function.

   To restrict every function that exists, revoke execute from both public and the role you want to restrict:

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

   To restrict every function created later, change the default privileges for both public and the role you want to restrict:

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

Add logs to help you debug a function. Logs matter most in a complex function.

Good targets to log include:

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

General logging#

Use the raise keyword to write custom logs to the Postgres logs in the Dashboard. Three severity levels appear by default:

• log
• warning
• exception (error level)

1
create function logging_example(
2
  log_message text,
3
  warning_message text,
4
  error_message text
5
)
6
returns void
7
language plpgsql
8
as $$
9
begin
10
  raise log 'logging message: %', log_message;
11
  raise warning 'logging warning: %', warning_message;
12
13
  -- immediately ends function and reverts transaction
14
  raise exception 'logging error: %', error_message;
15
end;
16
$$;
17
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:

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

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

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

For example:

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

You can also capture and modify an error message with the exception keyword:

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

Advanced logging#

For a more complex function, or for harder debugging, log the following:

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

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