Database functions

A database function is code stored in Postgres and run next to your data. Functions are useful when a task needs several statements to succeed or fail together, when you'd otherwise pull lots of rows into your app just to compute one answer, or when triggers and policies need shared logic. Anything in an exposed schema can also be called over HTTP through the Data API, which makes functions a simple way to add server-side endpoints without running a server.

A first function

For an invoicing tool, a function that totals an invoice:

create or replace function public.invoice_total(p_invoice_id uuid)
returns bigint
language sql
stable
set search_path = ''
as $$
  select coalesce(sum(round(quantity * unit_price_cents)), 0)::bigint
  from public.invoice_lines
  where invoice_id = p_invoice_id;
$$;

select public.invoice_total('3f6c8a52-1d7e-4f0a-9b8e-2c4d5e6f7a81');

language sql is the simplest choice for a single query. For branching, variables, loops or custom errors, use language plpgsql.

A PL/pgSQL function with checks

Marking an invoice as paid should only work for drafts or sent invoices, and should record when it happened. Doing it in one function means no client can skip a step.

create or replace function public.mark_invoice_paid(p_invoice_id uuid)
returns public.invoices
language plpgsql
security invoker
set search_path = ''
as $$
declare
  v_invoice public.invoices;
begin
  update public.invoices
  set status = 'paid', paid_at = now()
  where id = p_invoice_id
    and status in ('draft', 'sent')
  returning * into v_invoice;

  if v_invoice.id is null then
    raise exception 'Invoice % not found or not payable', p_invoice_id
      using errcode = 'P0001', hint = 'Only draft or sent invoices can be paid';
  end if;

  return v_invoice;
end;
$$;

This assumes a paid_at timestamptz column on invoices. Because it's security invoker, it runs with the caller's rights and RLS still decides which invoices they can update.

What a function can return

Volatility

Tell Postgres how the function behaves. It affects the planner, and the Data API uses it to decide whether the function may be called with GET.

• immutable: same inputs always give the same output and nothing is read from tables (for example a formatting helper).
• stable: reads tables but doesn't change anything during one statement (invoice_total above).
• volatile: the default; anything that writes or depends on time or randomness.

Security invoker or security definer

A security definer function in an exposed schema is an open door: anyone with the anon key can call it. Keep such functions in a schema that isn't exposed (for example private), check auth.uid() inside them, and revoke execute from roles that shouldn't call them. See security definer functions and RLS.

Always set search_path

Write set search_path = '' on every function and use fully qualified names (public.invoices, auth.uid()). Otherwise a function's behaviour can change depending on the caller's search path, which matters most for security definer. The MCP server's get_advisors tool flags functions in public that don't set it (function_search_path_mutable).

Who can execute a function

Postgres grants execute on every new function to PUBLIC, meaning every role, anon included. Restrict anything that shouldn't be callable by anonymous visitors:

revoke execute on function public.mark_invoice_paid(uuid) from public, anon;
grant execute on function public.mark_invoice_paid(uuid) to authenticated;

See How to revoke execute on a Postgres function.

Calling functions from your app

const { data: total, error } = await supabase.rpc('invoice_total', {
  p_invoice_id: '3f6c8a52-1d7e-4f0a-9b8e-2c4d5e6f7a81',
})

Argument names in the object must match the function's parameter names. The error raised with raise exception comes back in error.message, with your hint in error.hint. See Calling database functions (RPC).

Managing functions

• Studio lists functions under Database → Functions, where you can view and edit them. For anything you want to keep, put the create or replace function in a migration.
• Changing a signature (argument names or types, or the return type) needs drop function first, because create or replace can only change the body. Dropping and recreating resets grants, so repeat your revoke and grant lines.
• Overloading (two functions with the same name and different arguments) confuses the Data API. Give functions distinct names.
• Debugging: raise notice 'value: %', v_x; prints in Studio's SQL editor and in psql. To see where time goes, use Running EXPLAIN ANALYZE on functions.

Related

• Triggers
• Row Level Security
• Operations too complex for the client library