PostgREST / postgrest

REST API for any Postgres database
https://postgrest.org
MIT License
22.65k stars 1k forks source link

PostgREST ignoring composite attribute types on functions #3156

Open lmunoz-tc opened 5 months ago

lmunoz-tc commented 5 months ago

Environment

Description of issue

I have the following Postgres types and cast declarations:

CREATE TYPE api_ns.my_contact_spec AS (
  id TEXT,
  type TEXT
);

CREATE OR REPLACE FUNCTION api_ns.my_contact_spec_cast(api_ns.my_contact_spec)
RETURNS json AS $$ SELECT json_build_object('id', $1.id, 'type', $1.type); $$
LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION api_ns.my_contact_spec_cast(json)
RETURNS api_ns.my_contact_spec AS $$ SELECT ($1 #>> '{}')::api_ns.my_contact_spec; $$
LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION api_ns.my_contact_spec_cast(text)
RETURNS api_ns.my_contact_spec AS $$ SELECT api_ns.my_contact_spec_cast($1::json); $$
LANGUAGE SQL IMMUTABLE;

CREATE CAST ( api_ns.my_contact_spec AS json )
WITH FUNCTION api_ns.my_contact_spec_cast(api_ns.my_contact_spec) AS IMPLICIT;

CREATE CAST ( json AS api_ns.my_contact_spec )
WITH FUNCTION api_ns.my_contact_spec_cast(json) AS IMPLICIT;

CREATE CAST ( text AS api_ns.my_contact_spec )
WITH FUNCTION api_ns.my_contact_spec_cast(text) AS IMPLICIT;

With this, I expect to have a composite type my_contact_spec with the ability to be casted back and forth between JSON, TEXT and the type itself. I'm following the relevant documentation as far as I can tell.

Then, I declare a Postgres function that takes in my_contact_spec parameters:

CREATE OR REPLACE FUNCTION api_ns.create_thing ( name TEXT
                                               , contacts api_ns.my_contact_spec[]
                                               )
RETURNS other_namespace.some_table AS
$FUNC$
DECLARE r_retval other_namespace.some_table%ROWTYPE;
BEGIN
  -- Some SQL that ends up populating the r_retval variable using an
  -- INSERT INTO other_namespace.some_table   ... RETURNING * INTO r_retval
  RETURN r_retval;
END;
$FUNC$
LANGUAGE plpgsql VOLATILE;

In the Open API specification being generated by PostgREST (v12.0.2, from Docker) I am seeing the following definition for the contacts argument:

        "parameters": [
          {
            "format": "text",
            "in": "query",
            "name": "name",
            "required": true,
            "type": "string"
          },
          {
            "format": "api_ns.my_contact_spec[]",
            "in": "query",
            "name": "contacts",
            "required": true,
            "type": "string"
          }
        ],

When looking at the Swagger-UI generated example POST payload for this function, I see the following, directly derived from the definition above:

{
  "name": "string",
  "contacts": [
    "string"
  ]
}

I was expecting something more along the lines of:

{
  "name": "string",
  "contacts": [
    {
        "id": "string",
        "type": "string"
    }
  ]
}

I think PostgREST is not traversing the type hierarchy for function arguments, which is why it seems to be assuming the type of the contacts argument to be a string. Provided that my setup is correct, is this expected, or can it be considered a bug?

If my setup is incorrect, I would appreciate a pointer.

Thanks very much in advance.

laurenceisla commented 5 months ago

Yes, this is a current limitation in the OpenAPI output. We're currently working on the postgrest-openapi repository, so this will be added there.