Call functions from the API

Postgres functions in the public schema can be called through the REST API and the SDK.

#Create a function

create function public.count_open_todos() returns bigint
language sql stable as $$
  select count(*) from public.todos where not is_complete
$$;

#Call it

const { data, error } = await stitchdb.rpc('count_open_todos')

curl -X POST 'https://<project-ref>.p.stitchdb.com/rest/v1/rpc/count_open_todos' \
  -H "apikey: <anon-key>" \
  -H "Content-Type: application/json" \
  -d '{}'

Arguments are passed as a JSON object, for example stitchdb.rpc('search_todos', { term: 'ship' }).

#Permissions

Functions run with the caller's role (security invoker, the default), so Row Level Security applies to the tables they read. A security definer function runs with its owner's rights and skips RLS: give it a fixed search_path and revoke execute from roles that shouldn't call it.

create function public.admin_stats() returns json
language sql security definer set search_path = '' as $$
  select json_build_object('users', (select count(*) from auth.users))
$$;

revoke execute on function public.admin_stats() from anon, authenticated;