RPC & SQL functions

RPC (“remote procedure call”) lets you run a Postgres function over HTTP. You define a function in SQL; clients call it by name with named arguments. It's how you do anything a single query can't: multi-step logic in one transaction, complex joins/aggregations, or work you want to keep on the server.

Why RPC

The query builder sends exactly one statement per request. When you need several statements to run atomically (e.g. “decrement stock and create an order”), or logic that's awkward to express as a REST query, wrap it in a SQL function and call it as RPC. The whole function runs in one transaction.

1. Define a function

Create the function in the dashboard SQL editor. Arguments become the JSON body keys; the return type becomes the response.

-- A scalar-returning function
CREATE OR REPLACE FUNCTION public.count_posts(author uuid)
RETURNS integer
LANGUAGE sql STABLE
AS $$
  SELECT count(*)::int FROM public.posts WHERE author_id = author;
$$;

-- A table-returning function (search)
CREATE OR REPLACE FUNCTION public.search_posts(q text)
RETURNS SETOF public.posts
LANGUAGE sql STABLE
AS $$
  SELECT * FROM public.posts
  WHERE title ILIKE '%' || q || '%'
  ORDER BY created_at DESC
  LIMIT 50;
$$;

-- Multi-statement, transactional
CREATE OR REPLACE FUNCTION public.place_order(product uuid, qty int)
RETURNS uuid
LANGUAGE plpgsql
AS $$
DECLARE new_id uuid;
BEGIN
  UPDATE public.inventory SET stock = stock - qty WHERE id = product AND stock >= qty;
  IF NOT FOUND THEN RAISE EXCEPTION 'out of stock'; END IF;
  INSERT INTO public.orders (product_id, qty, user_id)
    VALUES (product, qty, auth.uid()) RETURNING id INTO new_id;
  RETURN new_id;
END;
$$;

2. Grant who can call it

A function is callable by a role only if that role has EXECUTE on it. The dashboard editor grants the project roles for you; if you create one elsewhere, grant explicitly:

GRANT EXECUTE ON FUNCTION public.search_posts(text) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION public.place_order(uuid, int) TO authenticated;

3. Call it

// scalar
const { data: n } = await ichi.rpc<number>('count_posts', { author: userId });

// table — returns rows
const { data: results } = await ichi.rpc('search_posts', { q: 'hello' });

// transactional, as the logged-in user (auth.uid() inside the fn)
await ichi.auth.login({ email, password });
const { data: orderId, error } = await ichi.rpc<string>('place_order', {
  product: productId, qty: 2,
});
if (error) console.error(error.detail); // e.g. "out of stock"

RPC and security

How a function interacts with RLS depends on how it's declared:

• SECURITY INVOKER(the default) — the function runs as the caller's role, so RLS on the tables it touches still applies, and auth.uid() is the caller. Prefer this.
• SECURITY DEFINER— runs as the function's owner and can bypass RLS. Use sparingly for trusted operations, always validate inputs, and set a safe search_path (e.g. SET search_path = public) to avoid hijacking.

Secrets in functions

Don't hardcode API keys or tokens in a function body. Store them as env vars (Functions → Env vars) and tick “Also readable in Postgres RPCs”. ichibase mirrors flagged vars into ichibase.secrets, which a function reads with ichibase.secret('KEY').

The secrets table is readable only by service_role — anon and authenticated get permission denied, so a client can never read them directly. To use a secret in an end-user-callable function, make it SECURITY DEFINER so it runs with service_role access and returns only what you choose to expose:

-- env var STRIPE_SECRET_KEY, flagged "readable in Postgres RPCs"
CREATE OR REPLACE FUNCTION public.charge(amount int)
RETURNS jsonb
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public
AS $$
DECLARE key text := ichibase.secret('STRIPE_SECRET_KEY');  -- never hardcoded
BEGIN
  -- ... use key (e.g. via an Edge Function / external call) ...
  RETURN jsonb_build_object('ok', true);
END;
$$;
GRANT EXECUTE ON FUNCTION public.charge(int) TO authenticated;

Return types & notes

• RETURNS SETOF table or TABLE(...) → an array of rows (same shape as a normal select).
• Scalar returns (integer, text, jsonb, …) → that value.
• Mark read-only functions STABLE (or IMMUTABLE); they can then be called with GET too. Functions that write must be VOLATILE (the default) and are called with POST.
• Raise errors with RAISE EXCEPTION— the message comes back in the error's detail.