create a mime handler for application/vnd.openxmlformats-officedocument.wordprocessingml.document

[fjf2002]

Environment

• PostgreSQL version: 16
• PostgREST version: 12.0.0
• Operating system: Debian bookworm

Description of issue

Hello @steve-chavez,

thanks a lot for reworking the media-type handlers.

Currently I'm trying to understand the docs at https://postgrest.org/en/v12.0/references/api/media_type_handlers.html#handler-function .

The first example does work for me; however when I replace everywhere application/vnd.twkb with application/vnd.openxmlformats-officedocument.wordprocessingml.document, I get the error HTTP/1.1 415 Unsupported Media Type with body

{"code":"PGRST107","details":null,"hint":null,"message":"None of these media types are available: application/vnd.openxmlformats-officedocument.wordprocessingml.document"}

I checked that I indeed have created the domain:

CREATE DOMAIN "application/vnd.openxmlformats-officedocument.wordprocessingml.document" AS bytea

What am I doing wrong?

[steve-chavez]

Hey @fjf2002,

Looks like that media type surpasses PostgreSQL identifier length:

CREATE DOMAIN "application/vnd.openxmlformats-officedocument.wordprocessingml.document" AS bytea;
NOTICE:  identifier "application/vnd.openxmlformats-officedocument.wordprocessingml.document" will be truncated to "application/vnd.openxmlformats-officedocument.wordprocessingml."

As a workaround you could use the "any" handler, which also works on functions as mentioned on #2188 (comment)

---

One solution for this could be allowing to return a wrapper composite type that has an enum:

CREATE TYPE spreadsheet_content AS (content bytea, mt excel_mt);

CREATE TYPE excel_mt AS ENUM (
  'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
,  'application/excel'
);

CREATE OR REPLACE FUNCTION .. RETURN spreadsheet_content ..

I don't see other way to define the media statically on the function.

[fjf2002]

@steve-chavez: Thanks. Let me see if I get this correctly.

Use case: Download a MS Word DOCX file, by means of a function with scalar return type.

As far as I understand,

• When the function RETURNS bytea, PostgREST will escape its output to JSON.
• When the function RETURNS a mime domain type derived from bytea like */* or application/whatever, PostgREST will output binary. So far, so good. Concerning the header,
  • In case of */* return type and */* or missing Accept header, PostgREST will send Content-Type: application/json.
  • In case of */* return type and any concrete Accept header, PostgREST will send that request header mime type as Response Content-Type. (Is this really what you want?)
  • I can modify the Content-Type of the response by setting PERFORM set_config('response.headers', '[{"Content-Type": "application/vnd.openxmlformats-officedocument.wordprocessingml.document"}]', true); inside the function.

As you said, unfortunately I cannot have a domain type application/vnd.openxmlformats-officedocument.wordprocessingml.document since this identifier is too long.

You suggested the workaround with the any handler */*. In my use case I have to define the any handler as

CREATE DOMAIN "*/*" as bytea;

I suppose this hinders me to use an "any handler" for any other function that would be based on any other data type in the whole database (for example, your example with the / xml handler at https://postgrest.org/en/v12.0/references/api/media_type_handlers.html#the-any-handler)?

[steve-chavez]

• In case of */* return type and */* or missing Accept header, PostgREST will send Content-Type: application/json.
• In case of */* return type and any concrete Accept header, PostgREST will send that request header mime type as Response Content-Type. (Is this really what you want?)

@fjf2002 Right, setting the Content-Type ourselves seemed convenient for the user but now is clear it's incorrect. How about if we instead make it always return application/octet-stream? That should be the most generic media type (SO link).

postgrest/src/PostgREST/Plan.hs

Lines 1008 to 1010 in 0c65bf4

	-- TODO resolving to "application/json" for "*/*" is not correct when using a "*/*" custom handler media type.
	-- We should return "application/octet-stream" as the generic type instead.
	defaultMTAnyToMTJSON = mapRight (\(x, y) -> (x, if y == MTAny then MTApplicationJSON else y))

I can modify the Content-Type of the response by setting PERFORM set_config('response.headers', '[{"Content-Type": "application/vnd.openxmlformats-officedocument.wordprocessingml.document"}]', true); inside the function.

Yes, correct.

I suppose this hinders me to use an "any handler" for any other function that would be based on any other data type in the whole database (for example, your example with the */* xml handler at https://postgrest.org/en/v12.0/references/api/media_type_handlers.html#the-any-handler)?

Just fixed that section to use a bytea instead (PostgREST/postgrest-docs#717).

[fjf2002]

@steve-chavez: Thanks very much for your quick fixes and clarification. That should quite make sense.

[steve-chavez]

How about if we instead make it always return application/octet-stream? That should be the most generic media type

Fixing this on #3103

As you said, unfortunately I cannot have a domain type application/vnd.openxmlformats-officedocument.wordprocessingml.document since this identifier is too long.

For now I'll document this is a limitation and recommend using the any handler as workaround. The enum approach (proposed above) looks too complicated.