# Calling database functions (RPC)

> Call Postgres functions through the SupaHost Data API with supabase.rpc() or plain HTTP: arguments, return types, filtering results, GET vs POST, errors and access control.

Source: https://supahost.app/docs/api/rpc · Updated 2026-11-11

Any function in an exposed schema can be called over HTTP at `/rest/v1/rpc/<function name>`. In supabase-js that's `supabase.rpc()`. RPC is the answer whenever a query is awkward to express with filters: aggregates, several writes that must happen together, logic that shouldn't be trusted to the client, or one round trip instead of five.

## A first call

A recipe app wants recipes that fit in a time budget, ranked by rating. Create the function in `public` (Studio's SQL editor, a migration, or MCP `apply_migration`):

```sql
create or replace function public.quick_recipes(max_minutes int, min_rating numeric default 0)
returns table (id bigint, title text, minutes int, rating numeric)
language sql
stable
set search_path = ''
as $$
  select r.id, r.title, r.minutes, avg(v.stars)::numeric(3, 2) as rating
  from public.recipes r
  left join public.recipe_votes v on v.recipe_id = r.id
  where r.minutes <= max_minutes
  group by r.id
  having coalesce(avg(v.stars), 0) >= min_rating
  order by rating desc nulls last;
$$;
```

Call it:

```ts
const { data, error } = await supabase.rpc('quick_recipes', { max_minutes: 30, min_rating: 4 })
```

Or over HTTP:

```bash
curl -X POST "https://app-one.acme.supahost.space/rest/v1/rpc/quick_recipes" \
  -H "apikey: $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"max_minutes": 30, "min_rating": 4}'
```

Argument names in the object must match the parameter names exactly. Parameters with a `default` can be left out.

## Return types and what you get back

| Function returns | `data` is |
|---|---|
| `int`, `text`, `boolean`, `jsonb`, ... | that value |
| a row type, like `public.recipes` | one object |
| `setof public.recipes` or `table (...)` | an array of objects |
| `void` | `null` |

## Filtering and paging results

When a function returns a set of rows, you can chain the same filters, ordering and paging as with `from()`. They're applied to the function's output:

```ts
const { data } = await supabase
  .rpc('quick_recipes', { max_minutes: 45 })
  .ilike('title', '%soup%')
  .order('minutes')
  .range(0, 19)
```

The 1000-row limit per request applies to RPC results too.

## GET or POST

supabase-js sends POST by default. Functions marked `stable` or `immutable` can also be called with GET, with arguments as query parameters, which lets browsers and CDNs cache them:

```ts
const { data } = await supabase.rpc('quick_recipes', { max_minutes: 30 }, { get: true })
```

```bash
curl "https://app-one.acme.supahost.space/rest/v1/rpc/quick_recipes?max_minutes=30" \
  -H "apikey: $SUPABASE_ANON_KEY"
```

A `volatile` function (the default, and required for anything that writes) only accepts POST.

## A function that writes

Booking a room should check for overlaps and insert in one step, so two people can't grab the same slot:

```sql
create or replace function public.book_room(p_room_id bigint, p_starts timestamptz, p_ends timestamptz)
returns public.bookings
language plpgsql
security invoker
set search_path = ''
as $$
declare
  v_booking public.bookings;
begin
  perform pg_advisory_xact_lock(p_room_id);  -- one booking at a time per room

  if exists (
    select 1 from public.bookings
    where room_id = p_room_id and tstzrange(starts_at, ends_at) && tstzrange(p_starts, p_ends)
  ) then
    raise exception 'Room % is already booked for that time', p_room_id using errcode = 'P0001';
  end if;

  insert into public.bookings (room_id, starts_at, ends_at)
  values (p_room_id, p_starts, p_ends)
  returning * into v_booking;

  return v_booking;
end;
$$;

revoke execute on function public.book_room(bigint, timestamptz, timestamptz) from public, anon;
grant execute on function public.book_room(bigint, timestamptz, timestamptz) to authenticated;
```

```ts
const { data: booking, error } = await supabase.rpc('book_room', {
  p_room_id: 3,
  p_starts: '2026-11-20T09:00:00Z',
  p_ends: '2026-11-20T10:00:00Z',
})

if (error) console.log(error.message) // "Room 3 is already booked for that time"
```

The whole function runs in one transaction: if anything raises an error, nothing it did is kept. Because it's `security invoker`, RLS still applies to the insert, and `user_id` gets its `default auth.uid()`. The overlap check here only sees bookings the caller's policies allow them to read; a check that must see every booking needs a `security definer` helper in a private schema. For a database-level guarantee, add an exclusion constraint on the table as well.

## Errors

| You see | Usually means |
|---|---|
| `PGRST202` "Could not find the function" | Wrong name or argument names, function not in an exposed schema, or schema cache not reloaded yet |
| `PGRST203` "Could not choose the best candidate function" | Two overloaded functions with the same name; rename one |
| `42501` permission denied for function | `execute` was revoked for this role |
| `P0001` with your message | Your own `raise exception` |

If a new function doesn't show up, reload the schema cache with `notify pgrst, 'reload schema';`. See [PostgREST not seeing new tables, columns or functions](/kb/postgrest-schema-cache).

## Access control

- Every new function is executable by `anon` unless you revoke it. Revoke and grant explicitly, as in the example above.
- `security invoker` functions (the default) obey RLS. `security definer` functions in `public` skip it and can be called by anyone with the anon key; keep those in a private schema. See [Database functions](/docs/database/functions) and [security definer functions and RLS](/kb/security-definer-functions).
- Only functions in exposed schemas (`public`, and the built-in `pgmq_public` wrappers) can be called. See [Exposed schemas](/docs/api/exposed-schemas).

## Related

- [Operations too complex for the client library](/kb/complex-operations-rpc)
- [Querying and filtering data](/docs/api/querying)
- [supabase-js rpc() reference](https://supabase.com/docs/reference/javascript/rpc)