Table-valued or composite types parameters for rpc functions

[Barrokgl]

• PostgreSQL version: image postgres:16-alpine
• PostgREST version: image postgrest/postgrest:latest
• Operating system: Mac OS Ventura 13.4

Description of issue

Does PostgREST rpc functions support table-valued or composite types parameters?

example code with composite type parameter:

CREATE TYPE api.my_param_type AS (
    "name" VARCHAR(255)
);

CREATE OR REPLACE FUNCTION api.example_function(my_param api.my_param_type) RETURNS void AS $$ 
BEGIN

END;
$$ LANGUAGE plpgsql;

as a result for composite types approach, parameters mapped as strings, which disallows function call with nested json

but works with string which contains columns separated by commas:

example code with table-valued parameter:

CREATE TABLE internal.company (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    "name" VARCHAR(255) NOT NULL,
    "description" TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE VIEW api.company AS
SELECT
    id,
    "name",
    description,
    created_at,
    updated_at
FROM
    internal.company;

CREATE OR REPLACE FUNCTION api.example_function_2(my_param api.company) RETURNS void AS $$ 
BEGIN
END;
$$ LANGUAGE plpgsql;

same behavoir with table-valued parameter, works fine, but looks unusual:

is it even documented behaviour (could not found anything in the docs) and is there is way to pass these parameters values as json objects? thanks

[wolfgangwalther]

is there is way to pass these parameters values as json objects?

Are you getting any error when you do so?

[Barrokgl]

is there is way to pass these parameters values as json objects?

Are you getting any error when you do so?

@wolfgangwalther yes, it works fine when calling directly there are no errors, but error occurs when relying on openapi schema, like with swagger ui or generated client. i'm getting an error because in the schema parameter type is defined as string:

generated openapi schema for function with compisite types:

{
  "/rpc/example_function": {
    "get": {
      "tags": [
        "(rpc) example_function"
      ],
      "produces": [
        "application/json",
        "application/vnd.pgrst.object+json;nulls=stripped",
        "application/vnd.pgrst.object+json"
      ],
      "parameters": [
        {
          "format": "api.my_param_type",
          "in": "query",
          "name": "my_param",
          "required": true,
          "type": "string"
        }
      ],
      "responses": {
        "200": {
          "description": "OK"
        }
      }
    },
    "post": {
      "tags": [
        "(rpc) example_function"
      ],
      "produces": [
        "application/json",
        "application/vnd.pgrst.object+json;nulls=stripped",
        "application/vnd.pgrst.object+json"
      ],
      "parameters": [
        {
          "in": "body",
          "name": "args",
          "required": true,
          "schema": {
            "properties": {
              "my_param": {
                "format": "api.my_param_type",
                "type": "string"
              }
            },
            "required": [
              "my_param"
            ],
            "type": "object"
          }
        },
        {
          "$ref": "#/parameters/preferParams"
        }
      ],
      "responses": {
        "200": {
          "description": "OK"
        }
      }
    }
  }
}

[wolfgangwalther]

Seems like a duplicate of #3156.

[Barrokgl]

@wolfgangwalther okay, thank you!