Call RPC

Call custom functions (Stored Procedures / Functions) in PostgreSQL database through the HTTP RESTful API.

💡 Tip: The PostgreSQL database HTTP API is based on the PostgREST protocol, and the RPC calling syntax is fully compatible with PostgREST.

Basic Syntax

POST https://your-envId.api.tcloudbasegateway.com/v1/rdb/rest/rpc/:function_name
Authorization: Bearer <access_token>
Content-Type: application/json

💡 Tip: For access_token, please refer to Get AccessToken

Create Functions

Before calling RPC, you need to create functions in the PostgreSQL database first. You can execute SQL through the DMC database management tool to create functions.

-- Create a simple addition function
CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
RETURNS integer AS $$
  SELECT a + b;
$$ LANGUAGE sql;

-- Create a function to search films
CREATE OR REPLACE FUNCTION search_films(keyword text)
RETURNS SETOF film AS $$
  SELECT * FROM film WHERE title ILIKE '%' || keyword || '%';
$$ LANGUAGE sql;

-- Create a statistics function
CREATE OR REPLACE FUNCTION get_film_stats()
RETURNS json AS $$
  SELECT json_build_object(
    'total', COUNT(*),
    'avg_duration', ROUND(AVG(duration)),
    'max_duration', MAX(duration),
    'min_duration', MIN(duration)
  ) FROM film;
$$ LANGUAGE sql;

Basic Calls

Call with Parameters

# Call the addition function
curl -X POST 'https://{{host}}/v1/rdb/rest/rpc/add_numbers' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{ "a": 1, "b": 2 }'

Response Example

HTTP/1.1 200 OK
Header:
    content-type: application/json; charset=utf-8
Body:
3

Call without Parameters

# Call the statistics function
curl -X POST 'https://{{host}}/v1/rdb/rest/rpc/get_film_stats' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json'

Response Example

HTTP/1.1 200 OK
Header:
    content-type: application/json; charset=utf-8
Body:
{
    "total": 15,
    "avg_duration": 148,
    "max_duration": 194,
    "min_duration": 96
}

Functions Returning Table Data

When a function returns SETOF <table>, the return value is in array format and supports the same filtering, sorting, pagination operations as regular queries.

# Search films containing a keyword
curl -X POST 'https://{{host}}/v1/rdb/rest/rpc/search_films' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{ "keyword": "Godfather" }'

Response Example

HTTP/1.1 200 OK
Header:
    content-type: application/json; charset=utf-8
Body:
[
    {
        "id": 3,
        "title": "The Godfather",
        "release_year": 1972,
        "director": "Francis Ford Coppola",
        "duration": 175,
        "_openid": "1977683311217119233"
    },
    {
        "id": 4,
        "title": "The Godfather: Part II",
        "release_year": 1974,
        "director": "Francis Ford Coppola",
        "duration": 202,
        "_openid": "1977683311217119233"
    }
]

Filtering and Sorting Results

When a function returns table data, you can use filtering, sorting, and pagination parameters just like regular queries.

# Search films, return only specified fields, sort by year descending, limit to 2 results
curl -X POST 'https://{{host}}/v1/rdb/rest/rpc/search_films?select=title,release_year&order=release_year.desc&limit=2' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-d '{ "keyword": "Godfather" }'

Response Example

HTTP/1.1 200 OK
Header:
    content-type: application/json; charset=utf-8
Body:
[
    {
        "title": "The Godfather: Part II",
        "release_year": 1974
    },
    {
        "title": "The Godfather",
        "release_year": 1972
    }
]

Using GET Requests

In addition to POST requests, you can also call RPC functions using GET requests, with parameters passed through query strings.

⚠️ Note: GET method is only suitable for read-only functions marked as IMMUTABLE or STABLE.

# Call the addition function using GET request
curl -X GET 'https://{{host}}/v1/rdb/rest/rpc/add_numbers?a=1&b=2' \
-H 'Authorization: Bearer <access_token>'

Response Example

HTTP/1.1 200 OK
Header:
    content-type: application/json; charset=utf-8
Body:
3

Force Single Object Response

By default, scalar values returned by functions are returned directly. If you need to force an object format response, specify the Header Accept: application/vnd.pgrst.object+json.

curl -X POST 'https://{{host}}/v1/rdb/rest/rpc/search_films?limit=1' \
-H 'Authorization: Bearer <access_token>' \
-H 'Content-Type: application/json' \
-H 'Accept: application/vnd.pgrst.object+json' \
-d '{ "keyword": "Godfather" }'

⚠️ Note: When using application/vnd.pgrst.object+json, the result must contain exactly one record, otherwise an error will be returned. Use with limit=1 to ensure this.

Response Example

HTTP/1.1 200 OK
Header:
    content-type: application/vnd.pgrst.object+json; charset=utf-8
Body:
{
    "id": 3,
    "title": "The Godfather",
    "release_year": 1972,
    "director": "Francis Ford Coppola",
    "duration": 175,
    "_openid": "1977683311217119233"
}

Function Parameter Types

PostgREST supports various PostgreSQL function parameter types:

Parameter Type	Description	JSON Example
integer	Integer	{ "id": 1 }
text / varchar	Text	{ "name": "test" }
boolean	Boolean	{ "active": true }
json / jsonb	JSON object	{ "data": { "key": "value" } }
integer[]	Integer array	{ "ids": [1, 2, 3] }
text[]	Text array	{ "names": ["a", "b"] }