# Database functions

> Write Postgres functions in SQL and PL/pgSQL on SupaHost, choose security invoker or definer, set a safe search_path, control who can execute them, and call them from your app.

Source: https://supahost.app/docs/database/functions · Updated 2026-11-11

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:

```sql
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.

```sql
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

| Return type | Use it for | Called from supabase-js returns |
|---|---|---|
| A scalar (`int`, `text`, `boolean`, `jsonb`) | One value | That value |
| A table's row type (`public.invoices`) | One row | An object |
| `setof public.invoices` | Many rows of an existing table | An array; you can add filters and ordering |
| `table (id uuid, total bigint)` | Many rows of a custom shape | An array |
| `void` | Side effects only | `null` |

## 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

| | `security invoker` (default) | `security definer` |
|---|---|---|
| Runs as | The caller (`anon`, `authenticated`, ...) | The function's owner |
| RLS | Applies | Usually bypassed (owner is `postgres`) |
| Typical use | Most functions | Narrow, audited tasks that need more rights than the caller, such as reading a membership table from inside a policy |

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](/kb/security-definer-functions).

## 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:

```sql
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](/kb/revoke-function-execute).

## Calling functions from your app

```ts
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)](/docs/api/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](/kb/explain-analyze-functions).

## Related

- [Triggers](/docs/database/triggers)
- [Row Level Security](/docs/database/row-level-security)
- [Operations too complex for the client library](/kb/complex-operations-rpc)