Calling Functions

An exposed PostgreSQL function is called with POST:

POST /api/public/function/create_invoice_from_work_order
{ "data": { "work_order": "8f1c…" } }

{ "data": "c41e…" }

The function needs EXECUTE permission (403 FUNCTION_EXECUTION_DENIED otherwise) and POST exposed. See Functions for writing and exposing one.

Arguments

Arguments go in data, by name: PostgreSQL's named notation, create_invoice_from_work_order(work_order => $1). Their order doesn't matter, and an argument with a DEFAULT can be left out.

CREATE FUNCTION quote_total(quote uuid, include_tax boolean DEFAULT true) RETURNS numeric …

POST /api/public/function/quote_total
{ "data": { "quote": "q9…" } }

A function without arguments takes { "data": {} }.

Only plain IN parameters are supported.

Results

POST /api/public/function/technicians_available
{ "data": { "branch": "0b6f…", "starts": "2026-10-15T09:00:00Z", "ends": "2026-10-15T12:00:00Z" } }

{ "data": [ { "technician": "e21a…", "name": "Martín Gómez", "skills": ["HVAC"] } ] }

Overloads

Each overload of a function is its own resource, with its own exposure and permission. A call is matched by its argument names. With no match, the answer is 404 FUNCTION_SIGNATURE_NOT_FOUND. If more than one overload fits, it's 422 AMBIGUOUS_FUNCTION. Give overloads different parameter names, or avoid them.

What runs, and as whom

A call runs in one transaction, with the caller's id in totalbase.actor_user_id (a trigger or the function can read it with current_setting('totalbase.actor_user_id', true)). It's audited with its arguments, and produces an EXECUTE event that workflows can react to.

Row filters and field permissions don't apply inside a function. What it can read and write is up to PostgreSQL: SECURITY INVOKER runs with TotalBase's database role, SECURITY DEFINER with the function owner's. Treat a function as an API of its own: check its arguments, and refuse with RAISE EXCEPTION. The message reaches the caller as 422 RAISE_EXCEPTION.

Asynchronous calls

A long call, such as recalculating every contract's next maintenance, can run in the background. Add Prefer: respond-async:

POST /api/public/function/plan_maintenance
Prefer: respond-async
{ "data": { "month": "2026-11-01" } }

202 { "data": { "id": "ex1…", "status": "PENDING", "result": null, "error": null } }

TotalBase checks permissions at once, so a call that could never run is refused immediately, and then queues it in PostgreSQL. Poll for the result:

GET /api/executions/ex1…

{ "data": { "id": "ex1…", "status": "COMPLETED", "result": 37, "error": null } }

• The queued call runs as you, with your roles as they are when it runs. If the role was removed, the token revoked or the function recreated in the meantime, it fails.
• A failed call isn't retried, because it may have done something outside its transaction.
• POST /api/executions/{id}/cancel cancels it: a queued call never starts, and a running one is interrupted and rolled back.
• Only the caller can see an execution. Anyone else gets 404.

Transactions accept Prefer: respond-async the same way.

Idempotency

Function calls accept an Idempotency-Key. For a function that must never run twice for the same request, like charging a payment, turn on Require an idempotency key under its Endpoints. See Writing.

The SQL it runs

This is the SQL TotalBase sent to PostgreSQL for the example above, as PostgreSQL's own log recorded it - ids shortened, long column lists cut with …. Every value travels as a parameter ($1), never inside the SQL text. The quote_total call above:

BEGIN;
SELECT set_config('statement_timeout', '30000', true),
       set_config('totalbase.actor_user_id', 'd039…', true);
SELECT "public"."quote_total"("p_quote" => $1) AS result;      -- $1 = the quote's id
COMMIT;

Arguments go by name (=>), so their order in the body doesn't matter, and PostgreSQL picks the function and casts each argument to its parameter's type.

Next steps

• Functions.
• OpenAPI: each function's arguments, documented.