Functions (RPC)

Call Postgres functions in the public schema over HTTP.

SQL

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

const { data } = 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 and results

SQL

create function public.search_todos(term text) returns setof public.todos
language sql stable as $$
  select * from public.todos where task ilike '%' || term || '%'
$$;

JavaScript

// Arguments go in the JSON body; set-returning functions can be filtered like tables
const { data } = await stitchdb.rpc('search_todos', { term: 'ship' }).eq('is_complete', false).limit(10)

// Read-only functions can also be called with GET
const { data: n } = await stitchdb.rpc('count_open_todos', {}, { get: true })

Functions run with the caller's role, so Row Level Security still applies. With read replicas, GET calls may be answered by a replica and POST calls always go to the primary. More in Call functions from the API.