Skip to content

Stored procedures (RPC)

Info

For simplicity, all the examples on this page, assume the HTTP client is already initialized and the requests are unauthenticated.

For an example of how to initialize the HTTP clients and make authenticated requests see authentication section

Every stored procedure in the exposed schema is accessible under the /rpc prefix 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:8080/rpc/add_two?a=2&b=3"
await fetch(endpoint+'/rpc/add_two?a=2&b=3');
await client.get('/rpc/add_two?a=2&b=3');
await client.query({ 
    query: gql` 
        query {
            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:8080/rpc/add_two
await fetch(endpoint+'/rpc/add_two', {
    method: 'POST',
    body: JSON.stringify({a:2,b:3})
});
await client.post('/rpc/add_two', {a:2,b:3});
await client.query({ 
    query: gql` 
        mutation {
            add_two(a: 2, b: 3)
        }
    `
});

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:8080/rpc/add_two?columns=a,b
await fetch(endpoint+'/rpc/add_two?columns=a,b', {
    method: 'POST',
    headers: new Headers({'Prefer': 'params=multiple-objects'})},
    body: JSON.stringify([{"a":2,"b":3},{"a":4,"b":5}])
});
const options = {headers: {'Prefer': 'params=multiple-objects'}}
await client.post('/rpc/add_two?columns=a,b', [{"a":2,"b":3},{"a":4,"b":5}], options);

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 /rpc/add_two?a=2&b=3

5
GET /rpc/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:8080/rpc/all_active_projects?select=name&id=gt.2"
await fetch(endpoint+'/rpc/all_active_projects?select=name&id=gt.2');
await client.get('/rpc/all_active_projects?select=name&id=gt.2');
await client.query({ 
    query: gql` 
        query {
            all_active_projects(where: {id: {gt: 2}}){
                name
            }
        }
    `
});