Calling database functions (RPC)

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

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:

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

Or over HTTP:

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

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:

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:

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

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:

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;

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

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.

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 and security definer functions and RLS.
• Only functions in exposed schemas (public, and the built-in pgmq_public wrappers) can be called. See Exposed schemas.

Related

• Operations too complex for the client library
• Querying and filtering data
• supabase-js rpc() reference