More flexible SQL media type handlers

[steve-chavez]

Problem

The SQL handlers introduced on #2825 are not flexible enough to handle multiple media types or the any */* media type. Like the example on https://postgrest.org/en/stable/how-tos/create-soap-endpoint.html, which needs to handle text/xml, application/soap+xml and */*.

Solution

Using the in-db config. Map the handlers in the following way:

• Use oids for identifying the database objects. This way we don't get a conflict between a table and proc named the same.
• A json structure of {"<table or proc oid>": {"<handler oid>": ["array", "of", "media types" ]}}
• For functions the handlers will be other regular functions (usually just a simple cast), for tables the handlers will be aggregates.

create or replace function postgrest.pre_config()
returns void as $$
select
  set_config('pgrst.handlers',
    json_build_object(
     'test.my_soap_endpoint'::regproc::oid, json_build_object(
        'xml_in'::regproc::oid, array['text/xml', 'application/soap+xml', '*/*']
      ),
     'test.projects'::regclass::oid, json_build_object(
       'json_agg'::regproc::oid, array['application/json', '*/*']
      )
    )::text
  , true
  );
$$ language sql;

Using oids like above we ensure the user gets some type checking to prevent errors.

Alternatives

• There's a proposal involving CASTs on Add pgrst.accept setting to RPC #1582 (comment), but that seems more complicated to implement and harder to track of (with the above it's all defined in a single place). Additionally the mapping it provides it's global, cannot be scoped to a single function or table handler.

[fjf2002]

@steve-chavez: Thank you for the suggestion.

If I could choose, I would favour a solution inside the test.my_soap_endpoint function.

The easiest from my (perhaps somewhat simplistic) standpoint would be: If a function returns xml, the HTTP response shall be text/xml. No matter what Accept Header came in the request.
The same could apply for text->text/csv and bytea->application/octet-stream(?)

Your solution also works and gives much more flexibility, but has to be configured.

[steve-chavez]

he easiest from my (perhaps somewhat simplistic) standpoint would be: If a function returns xml, the HTTP response shall be text/xml. No matter what Accept Header came in the request.
The same could apply for text->text/csv and bytea->application/octet-stream(?)

@fjf2002 Yeah, that could work too. So if there's a custom media type handler it should take precedence over the json handler defined for */*.

The only thing that doesn't cover is also handling the application/soap+xml media type. But maybe that's not so critical for you?

[fjf2002]

@steve-chavez:

The only thing that doesn't cover is also handling the application/soap+xml media type. But maybe that's not so critical for you?

Yes, that's not critical for me.

I must admit one other thing:
The project I was doing, when I was after the XML features and when I wrote the SOAP howto, now is in a dead state.
I personally do not currently need the XML or SOAP feature. However I thought fully supporting these features still would be great for others. (You do know if or how many people use PostgREST with XML or SOAP?)

So feel free to give this issue low priority.

Yeah, that could work too. So if there's a custom media type handler it should take precedence over the json handler defined for */*.

Do you mean with or without any configuration inside postgrest.pre_config() ortest.my_soap_endpoint() ?

Regards,
fjf2002

[steve-chavez]

Do you mean with or without any configuration inside postgrest.pre_config() or test.my_soap_endpoint() ?

Meant inside test.my_soap_endpoint. I'll close this and follow up on #2188