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().

• Database functions vs Edge Functions compares the two. Start here if you aren't sure which one fits.
• Create a database function has the steps, from a one-line function to one that takes parameters.
• Secure a database function covers which user the function runs as and who can call it.
• Debugging database functions covers logging and error handling.

Quick demo

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.

Create a database function

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 [#simple-functions]

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

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

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

select hello_world();

const { data, error } = await supabase.rpc('hello_world')

final data = await supabase
  .rpc('hello_world');

try await supabase.rpc("hello_world").execute()

val data = supabase.postgrest.rpc("hello_world")

data = supabase.rpc('hello_world').execute()

await supabase.Rpc("hello_world", null);

Returning data sets

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

For example, create two tables holding some Star Wars data. The Data tab shows the rows they end up with.

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

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

create table planets (
  id serial primary key,
  name text
);

insert into planets
  (name)
values
  ('Tatooine'),
  ('Alderaan'),
  ('Kashyyyk');

create table people (
  id serial primary key,
  name text,
  planet_id bigint references planets
);

insert into people
  (name, planet_id)
values
  ('Anakin Skywalker', 1),
  ('Luke Skywalker', 1),
  ('Princess Leia', 2),
  ('Chewbacca', 3);

The following function returns all the planets:

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

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

select *
from get_planets()
where id = 1;

const { data, error } = supabase.rpc('get_planets').eq('id', 1)

final data = await supabase
  .rpc('get_planets')
  .eq('id', 1);

let response = try await supabase.rpc("get_planets").eq("id", value: 1).execute()

val data = supabase.postgrest.rpc("get_planets") {
    filter {
        eq("id", 1)
    }
}

data = supabase.rpc('get_planets').eq('id', 1).execute()

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.

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

  return new_row;
end;
$$;

You can run this function inside your database with a select query, or with the client libraries:

select * from add_planet('Jakku');

const { data, error } = await supabase.rpc('add_planet', { name: 'Jakku' })

final data = await supabase
  .rpc('add_planet', params: { 'name': 'Jakku' });

struct Planet: Encodable {
  let name: String
}

try await supabase.rpc(
  "add_planet",
  params: Planet(name: "Jakku")
)
.execute()

try await supabase.rpc(
  "add_planet",
  params: ["name": AnyJSON.string("Jakku")]
)
.execute()

val data = supabase.postgrest.rpc(
    function = "add_planet",
    parameters = buildJsonObject { //You can put here any serializable object including your own classes
        put("name", "Jakku")
    }
)

data = supabase.rpc('add_planet', params={'name': 'Jakku'}).execute()

await supabase.Rpc("add_planet", new Dictionary<string, object> { { "name", "Jakku" } });

Secure a database function

Security definer vs invoker

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

create or replace function hello_world()
returns text
language plpgsql
security definer set search_path = ''
as $$
begin
  return 'hello world';
end;
$$;

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:

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

   revoke execute on all functions in schema public from public;
   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. Postgres grants execute to PUBLIC through a global default, so revoke it without in schema. A revoke limited to one schema has no effect on a global default:

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

   The first statement applies to every function your role creates later, in every schema. Grant execute on each new function to the roles that call it, including helper functions that your Row Level Security policies call.

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

grant execute on function public.hello_world to authenticated;

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

Create a database function in the Dashboard

Call a database function from JavaScript

Call an external API from a database function