openapi: 3.0.0 info: title: PostgreSQL RESTful API version: 1.0.0 description: >- ### Function Introduction The Data API of CloudBase PostgreSQL database is based on the open-source [PostgREST](https://postgrest.org). For more documentation, please refer to the [official PostgREST documentation](https://postgrest.org/en/stable/). ### Request Domain The request URL format is: `https://{envId}.api.tcloudbasegateway.com/v1/rdb/rest/{table}` - envId is the environment ID - table is the table name ### Integration Guide To call the following APIs, you need to pass an AccessToken in format `Authorization: Bearer `. For token acquisition methods, please refer to: https://docs.cloudbase.net/http-api/basic/access-token ### Request Encoding Please perform URL encoding before making requests, for example: > Original request ```shell curl -i -X GET 'https://{{host}}/v1/rdb/rest/course?select=name,position&name=like.%张三%&title=eq.文章标题' ``` > Encoded request ```shell curl -i -X GET 'https://{{host}}/v1/rdb/rest/course?select=name,position&name=like.%%E5%BC%A0%E4%B8%89%&title=eq.%E6%96%87%E7%AB%A0%E6%A0%87%E9%A2%98' ``` ### Request Headers and Response Headers
Request Header Parameters Description Example
Accept Supports `application/json`, `application/vnd.pgrst.object+json` Controls data return format Accept: application/json
Content-Type Supports `application/json`, `application/vnd.pgrst.object+json` Return Content-Type: application/json
Prefer Characteristic values that operations depend on - `return=representation` Write operation, returns data body and headers - `return=minimal` Write operation, returns only headers without data body, default type for write operations - `count=exact` Read operation, specifies count calculation - `resolution=merge-duplicates` Upsert operation, merges conflicting items - `resolution=ignore-duplicates` Upsert operation, ignores conflicting items Prefer: return=representation
Preference-Applied Prefer type used in request Response information uses Prefer from request, will return corresponding characteristic information, may have default values in certain cases Preference-Applied: return=representation
Content-Range Pagination information - When querying with Prefer: count=exact, pagination information like 0-1/2 will be returned, where 0 indicates starting position, 1 indicates offset, and 2 indicates total count (Note: The starting position and offset here are not the meanings of offset and limit keywords in PostgreSQL, but refer to the data position after query, usually used with limit) - When specifying return=representation or return=minimal (default) for write operations, pagination information like *-*/2 will be returned, where 2 indicates the number of affected rows Content-Range: 0-1/2
### Error Codes and HTTP Status Codes
Error Code HTTP Status Code Description
INVALID_PARAM 400 Invalid request parameter
INVALID_REQUEST 400 Invalid request content: missing permission fields, SQL execution exceptions, etc.
INVALID_REQUEST 406 Does not meet single record return constraint
PERMISSION_DENIED 401、403 Authentication failed: returns 401 if identity authentication fails, returns 403 if authorization fails
RESOURCE_NOT_FOUND 404 Database instance or table information not found
SYS_ERR 500 System internal error
OPERATION_FAILED 503 Failed to establish database connection
RESOURCE_UNAVAILABLE 503 Database unavailable due to certain reasons
### Data Return 1. For all POST, PATCH, DELETE, request header with `Prefer: return=representation` indicates there is a response body, absence indicates no response package, only response headers 2. Response body of POST, PATCH, DELETE is usually JSON array type `[]`. If request header specifies `Accept: application/vnd.pgrst.object+json`, then JSON object type `{}` will be returned 3. If specifying `Accept: application/vnd.pgrst.object+json`, but the data amount is greater than 1, an error will be reported tags: - name: 数据操作 description: Data CRUD operations servers: - url: https://{envId}.api.tcloudbasegateway.com description: TCB openapi endpoint variables: envId: default: "your-envId" description: Environment ID paths: /v1/rdb/rest/rpc/{function_name}: post: tags: - 数据操作 operationId: rpcCall summary: RPC Call description: Call stored procedures or custom functions in PostgreSQL (powered by PostgREST RPC). Supports passing parameters and returns function execution results. parameters: - name: function_name in: path description: Stored procedure or function name required: true schema: type: string example: "my_function" - name: select in: query description: Return fields, supports * or specified field list, used to filter columns returned by the function required: false schema: type: string - name: limit in: query description: Limit return count (effective when function returns a set) required: false schema: type: integer - name: offset in: query description: Offset, used for pagination (effective when function returns a set) required: false schema: type: integer - name: order in: query description: Sort field, format is field.asc or field.desc (effective when function returns a set) required: false schema: type: string - name: Prefer in: header description: |- Preference settings: - `params=single-object` passes the request body as a single JSON parameter to the function - `count=exact` returns exact count required: false schema: type: string requestBody: required: false content: application/json: schema: type: object description: Function parameters, passed as a JSON object where keys are parameter names and values are parameter values examples: named_params: summary: Named parameter call value: param1: "value1" param2: 42 single_object: summary: "Single object parameter (requires Prefer: params=single-object)" value: key1: "value1" key2: "value2" responses: "200": description: Call successful content: application/json: schema: oneOf: - type: array items: type: object description: Function returns a set - type: object description: Function returns a single object - type: string description: Function returns a scalar value "400": description: Request error, such as function not found or parameter error content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" "401": description: Authentication failed content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" "404": description: Function does not exist content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" /v1/rdb/rest/{table}: get: tags: - 数据操作 operationId: queryRecords summary: Query Records description: Query data in table, supports complex queries, join queries, pagination, sorting, etc. parameters: - name: table in: path description: Table name required: true schema: type: string - name: select in: query description: Select fields, supports * or specified field list, supports join queries like class_id(grade,class_number) required: false schema: type: string example: "*" - name: limit in: query description: Limit return count required: false schema: type: integer example: 10 - name: offset in: query description: Offset, used for pagination required: false schema: type: integer example: 0 - name: order in: query description: Sort field, format is field.asc or field.desc required: false schema: type: string example: "id.asc" - name: Prefer in: header description: Preference setting, such as count=exact required: false schema: type: string responses: "200": description: Query successful headers: Content-Range: description: Data range schema: type: string example: "0-9/100" content: application/json: schema: type: array items: type: object "400": description: Request error content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" "401": description: Authentication failed content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" "404": description: Table does not exist content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" head: tags: - 数据操作 operationId: countRecords summary: Count Data Records description: Count data records in table that match conditions parameters: - name: table in: path description: Table name required: true schema: type: string - name: select in: query description: Select fields required: false schema: type: string - name: Prefer in: header description: Must be set to count=exact required: true schema: type: string example: "count=exact" responses: "200": description: Count successful headers: Content-Range: description: Data range and total count schema: type: string example: "0-9/100" post: tags: - 数据操作 operationId: insertRecords summary: Insert or Upsert Records description: Insert single or multiple records, supports upsert operation parameters: - name: table in: path description: Table name required: true schema: type: string - name: select in: query description: Return fields, supports * or specified field list required: false schema: type: string - name: Prefer in: header description: Preference settings. `return=representation` returns the inserted data; `resolution=merge-duplicates` enables upsert and merges (updates) existing rows on primary key or unique constraint conflict (equivalent to `ON CONFLICT ... DO UPDATE`); `resolution=ignore-duplicates` enables upsert and skips conflicting rows without modifying existing data (equivalent to `ON CONFLICT ... DO NOTHING`). required: false schema: type: string requestBody: required: true content: application/json: schema: oneOf: - type: object description: Single data - type: array items: type: object description: Multiple data examples: single: summary: Insert single data value: name: "Rust" credits: 5 created_by: "e65c2ef5-d0bd-485d-a7b4-830789f00638" batch: summary: Batch insert value: - name: "A" created_by: "e65c2ef5-d0bd-485d-a7b4-830789f00638" - name: "B" credits: 5 created_by: "e65c2ef5-d0bd-485d-a7b4-830789f00638" upsert: summary: Upsert operation value: id: 113 name: "c" responses: "201": description: Insert successful headers: Content-Range: description: Number of affected rows schema: type: string example: "*/1" Preference-Applied: description: Applied preference settings schema: type: string example: "return=minimal" content: application/json: schema: oneOf: - type: array items: type: object - type: object "400": description: Request error content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" patch: tags: - 数据操作 operationId: updateRecords summary: Update Records description: Update data according to conditions, WHERE conditions must be provided parameters: - name: table in: path description: Table name required: true schema: type: string - name: select in: query description: Return fields, supports * or specified field list required: false schema: type: string - name: limit in: query description: Limit update count required: false schema: type: integer - name: order in: query description: Sort field required: false schema: type: string - name: Prefer in: header description: Preference setting, such as return=representation returns updated data required: false schema: type: string requestBody: required: true content: application/json: schema: type: object example: name: "Java" credits: 1 responses: "200": description: Update successful (with response body) headers: Content-Range: description: Number of affected rows schema: type: string example: "*/1" Preference-Applied: description: Applied preference settings schema: type: string content: application/json: schema: type: array items: type: object "204": description: Update successful (without response body) headers: Content-Range: description: Number of affected rows schema: type: string example: "*/1" Preference-Applied: description: Applied preference settings schema: type: string "400": description: Request error, such as missing WHERE conditions content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" delete: tags: - 数据操作 operationId: deleteRecords summary: Delete Records description: Delete data according to conditions, WHERE conditions must be provided parameters: - name: table in: path description: Table name required: true schema: type: string - name: select in: query description: Return fields, supports * or specified field list required: false schema: type: string - name: limit in: query description: Limit delete count required: false schema: type: integer - name: order in: query description: Sort field required: false schema: type: string - name: Prefer in: header description: Preference setting, such as return=representation returns deleted data required: false schema: type: string responses: "200": description: Delete successful (with response body) headers: Content-Range: description: Number of affected rows schema: type: string example: "*/1" Preference-Applied: description: Applied preference settings schema: type: string content: application/json: schema: type: array items: type: object "204": description: Delete successful (without response body) headers: Content-Range: description: Number of affected rows schema: type: string example: "*/1" Preference-Applied: description: Applied preference settings schema: type: string "400": description: Request error, such as missing WHERE conditions content: application/json: schema: $ref: "#/components/schemas/ErrorResponse" components: schemas: ErrorResponse: type: object properties: code: type: string description: Error code example: "PGRST106" details: type: string nullable: true description: Error details example: null hint: type: string nullable: true description: Error hint example: "Only the following schemas are exposed: public" message: type: string description: Error message example: "Invalid schema: undefined"