OpenAPI Output: Functions Returning a Table not showing parameters

[pbel78]

Problem

We are exposing our data trough PostgREST using Postgres functions with functions using a table return type:

CREATE OR REPLACE FUNCTION dvb_core_gui.configs()
 RETURNS TABLE(config_id text, config_group text, config_value jsonb)
 LANGUAGE sql
 SECURITY DEFINER
AS $function$
select 'config key'::text as config_id, 'config group'::text config_group, '{"key":"value"}'::jsonb config_value 

      $function$
;

That does work fine including filtering, limiting, grouping etc. But if I look at the OpenAPI output I see:

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

Solution

I think Postgrest should read also the metadata of the functions and offering them to the OpenAPI output.

I get the necessary metadata from here:

SELECT 
    p.proname AS table_id,
    a.name AS column_id,
     format_type(t.oid, NULL) as  data_type
FROM pg_proc p
JOIN pg_namespace n 
  ON p.pronamespace = n.oid
-- names and types with ordinality give us the per-parameter position
JOIN unnest(p.proargnames) WITH ORDINALITY AS a(name, ordinality)
  ON TRUE
JOIN unnest(p.proallargtypes) WITH ORDINALITY AS t(oid, ordinality2)
  ON a.ordinality = t.ordinality2
-- include only output columns (OUT, INOUT, TABLE)
JOIN unnest(p.proargmodes) WITH ORDINALITY AS m(mode, ordinality3)
  ON a.ordinality = m.ordinality3
WHERE  p.proname = 'configs'
  AND m.mode IN ('o','b','t')   -- OUT, INOUT, TABLE
ORDER BY p.proname, a.ordinality;

Sample Output:

configs	config_id	text	
configs	config_group	text	
configs	config_value	jsonb	

[laurenceisla]

Just to note that this is implemented in the postgrest-openapi repo. But it doesn't looks like a trivial fix within PostgREST.