Database functions

A database function is code stored in your project's Postgres database and run there, next to the data. Use one when several writes must happen together or not at all, when the same logic is needed in many queries or policies, or as the code a trigger runs. For code that calls other services, sends email or takes more than a few seconds, use Edge Functions instead.

#In the console

Schema › Functions lists the functions in the selected schema. Functions that came with extensions (pgcrypto, pg_trgm…) and the few Sovaria keeps in your schema for documents and table history are hidden; Show N from extensions and Sovaria lists them too.

Click a function to see its full definition, comment and facts, copy it, or Open in SQL editor to try it. The row menu has Edit and Delete.

#Create a function

1. 1

   New function

   Under Schema › Functions, click New function. It is created in the schema selected at the top of the page.

2. 2

   Name and arguments

   Lowercase with underscores. Add arguments with a name, a type (uuid, text, integer, numeric, jsonb, timestamptz…) and an optional default. Callers over REST pass arguments by name, so name them well.

3. 3

   Returns

   Pick a type, void for nothing, setof <table> for rows of a table, or trigger for a function a trigger runs (it then takes no arguments).

4. 4

   Language, behaviour and who it runs as

   PL/pgSQL for variables, if and loops; SQL for a single query (faster to plan). Behaviour and runs-as are explained below; the defaults (volatile, caller) are safe.

5. 5

   Body and description

   Write the body. The description is saved as the function's comment and shown in the list.

6. 6

   Check, then create

   Check runs the change in a transaction that is rolled back, so a typo is caught without changing anything. Create function applies it as a recorded migration: it shows in Version history: see, undo and restore changes, where you can undo it.

The panel shows the exact SQL it will run and the SQL that undoes it. Changing functions, triggers and indexes needs a project owner or admin, as any schema change does (Migrations).

-- Count a user's published posts.
create or replace function post_count(u uuid)
returns integer
language sql
stable
as $
  select count(*)::int from posts where author_id = u and published
$;

#What a function can return

#Behaviour: volatile, stable, immutable

Behaviour tells Postgres how often it needs to run the function. Getting it right lets Postgres cache results and use the function in indexes; getting it wrong can return stale results.

#Who a function runs as

A security definer function runs with more rights than its caller, so keep it small and check the caller inside it with auth.uid(). The console adds set search_path = '' to definer functions, so a caller can't slip in their own table or function with the same name; write every name with its schema (public.posts, not posts).

create or replace function public.join_team(invite text)
returns uuid
language plpgsql
security definer
set search_path = ''
as $
declare
  t uuid;
begin
  if auth.uid() is null then
    raise exception 'sign in first' using errcode = '42501';
  end if;
  select team_id into t from public.team_invites where code = invite and expires_at > now();
  if t is null then
    raise exception 'invite not found or expired' using errcode = 'P0002';
  end if;
  insert into public.team_members (team_id, user_id) values (t, auth.uid()) on conflict do nothing;
  return t;
end;
$;

#Call a function

// Named arguments, exactly as the function declares them.
const n = await sovaria.data.rpc('post_count', { u: userId })              // → 12
await sovaria.data.rpc('transfer_points', { from_user: from, to_user: to, amount: 50 })

// Functions that only read can use GET; functions that return rows take PostgREST parameters.
const top = await sovaria.data.rpc('top_posts', { days: 7 }, { get: true, query: { select: 'id,title', limit: 10 } })

• POST /rest/v1/rpc/<name> takes the arguments by name as a JSON object; GET takes them as query parameters, for functions that only read (HTTP API).
• A function that returns one value answers with that value; one that returns rows answers with an array, which you can filter, order and limit like a table (?select=id,title&order=id.desc&limit=10).
• App users can call a function once an access policy allows the resource fn:<name> — nothing allows it by default (Row-level security and access). The function's own queries still run under row-level security unless it is a security definer.
• Every SDK has the helper: sovaria.data.rpc() in JavaScript (JavaScript: Database and documents), rpc() in Python (Python: Database and documents), and db.rpc() in Dart, Swift and Kotlin. An error the function raises comes back with the database's code and message in the error's details.
• Server code with the service role key can call any function.

#Functions in policies and defaults

A stable function keeps row-level security policies short and fast. Make it a security definer when it has to read a table the caller can't, such as the membership table itself:

create or replace function public.is_member(team uuid)
returns boolean
language sql
stable
security definer
set search_path = ''
as $
  select exists (select 1 from public.team_members where team_id = team and user_id = auth.uid())
$;

create policy "members read team projects" on projects
  for select using (public.is_member(team_id));

Functions also work as column defaults (default gen_random_uuid(), default auth.uid()) and in generated columns and index expressions when they are immutable.

#Change or delete a function

• Edit opens the full definition. Saving runs it as create or replace, and undo restores the previous definition.
• Postgres can't change a function's return type or argument names in place (42P13). Changing the argument types creates a second function with the same name (an overload). In both cases, delete the old one and create the new one.
• Delete is refused while a trigger, view or policy still uses the function (2BP01); remove those first. Undo re-creates it from its saved definition.
• Functions from extensions are managed by the extension: turn the extension off to remove them (Extensions).

#Limits

Your plan limits how many functions and procedures a project has, across all your schemas. Functions that came with an extension and Sovaria's own (sov_*) don't count. Creating one past the limit fails with 54000 and says which plan raises it.

A function runs inside the statement that called it, so the statement timeout covers it (Pooling and timeouts).

#Errors

#Coming from Firebase or Supabase?

Moving an existing app? Migrate from Supabase or Firebase imports users (with their passwords), data, files and rules.