RPC

Every stored procedure in the exposed schema is accessible provided the user is authorized to call it. The stored procedures can be called by making a POST http request and in some cases a GET request

Procedure declaration

Procedures must be declared with named parameters.

CREATE FUNCTION add_two(a integer, b integer) RETURNS integer AS $$
 SELECT a + b;
$$ LANGUAGE SQL IMMUTABLE;

Calling procedures using GET

Procedures declared as STABLE or IMMUTABLE (see volatility) can be called using GET requests

curl "http://localhost:3000/rest/add_two?a=2&b=3"

Calling procedures using POST

All available stored procedures can be called using POST.

curl -X POST  \
-d '{"a":2,"b":3}' \
http://localhost:3000/rest/add_two

Bulk calling

It's possible to call the same stored procedure multiple times with different parameters, in the same HTTP request by adding the Prefer: params=multiple-objects header

curl -X POST  \
-H "Prefer: params=multiple-objects"
-d '[{"a":2,"b":3},{"a":4,"b":5}]' \
http://localhost:3000/rest/add_two?columns=a,b

Response shape

Procedures can return scalars, custom types, table rows or sets or rows. The procedure response type is detected and the response payload is shaped accordingly.

GET /rest/add_two?a=2&b=3

5

GET /rest/all_active_projects

[
  { "id": 1, "name": "Landing page"},
  { "id": 2, "name": "UI design"},
  { "id": 3, "name": "Infrastructure architecture"}
]

Procedures that return table type responses (a set or rows from a table) can be shaped using the select parameter and filtered as any other view or table.

curl "http://localhost:3000/rest/all_active_projects?select=name&id=gt.2"