Custom media types + conversion for `Content-Type`

[steve-chavez]

Problem

Currently we allow handling raw input for application/json, text/xml, text/plain, application/octet-stream on functions(ref).

However it's inflexible, no other custom media types can be handled. It also doesn't work for tables/views.

Solution

Use a domain-based media type as a function unnamed parameter to do custom conversion. Unlike ref this can work for both functions and tables/views.

Functions interface

CREATE DOMAIN "application/json" as json;
CREATE DOMAIN "application/octet-stream" as bytea;

CREATE FUNCTION mult_them("application/json") RETURNS int AS $$
  SELECT ($1->>'x')::int * ($1->>'y')::int
$$ LANGUAGE SQL;

CREATE FUNCTION upload_binary("application/octet-stream") RETURNS void AS $$
  INSERT INTO files(blob) VALUES ($1);
$$ LANGUAGE SQL;

Tables/views interface

CREATE FUNCTION convert_projects("application/json") RETURNS projects AS $$
-- ...
$$ LANGUAGE SQL;

CREATE FUNCTION convert_projects("text/plain") RETURNS projects AS $$
-- ...
$$ LANGUAGE SQL;


CREATE FUNCTION convert_projects("text/xml") RETURNS projects AS $$
-- ...
$$ LANGUAGE SQL;

e.g. the first function will be used when doing:

POST /projects 
Content-Type: application/json

This means we'll use the unnamed parameter + return type to apply a conversion function for a particular table/view.

Inside the function a json_to_recordset can be used (this is what we do internally). With this interface the user can override our default behavior.

Thanks to overloaded functions the same function name can be used to handle different media types.

Notes

• Consider dropping Functions with a single unnamed parameter after solving this. Advantages:
  • This new interface is more intuitive and less magical.
  • Simplify error messages.
  • Simplify searching the rpc function.
  • It will also be consistent with SQL handlers for custom media types #2825.
  • The forward migration is relatively simple - changing a parameter type.
• The domain-based media types idea comes from Add pgrst.accept setting to RPC #1582 and Data Representations #2523. (long threads) .

Related

• Asymetric CSV encoding/decoding #1043
  • https://github.com/rin-nas/postgresql-patterns-library/blob/master/functions/csv_parse.sql
  • https://stackoverflow.com/questions/56834123/wanted-an-alternative-to-copy-to-from-for-pl-pgsql-function
• feat: support XML? #2188

[steve-chavez]

@wolfgangwalther Any feedback on this one? Doing it will involve a big refactor. So I'm wondering if you see anything wrong before I start.

[wolfgangwalther]

I did not have the time, yet, to follow along what exactly has been implemented for Accept already. Am I right that currently it's only possible to say RETURNS "media/type" for functions using a domain media/type? So we don't support any of the CAST idea, yet, but all the pieces are in place to be able to extend that with casts later on?

Assuming the above is correct, then:

• The functions interface suggested in this issue seems like exactly the same thing, just for input via Content-Type. It will be extensible the same way with CASTs. 👍
• However, the proposed table/view interface looks different. In which schema are those functions defined? If they are defined in the exposed schema, then what's the difference between those functions and a regular RPC ("function interface style")? If they are not in the exposed schema, how do we detect them?

The answer to those questions in the second paragraph is: Casts. Those functions are effectively implementing a cast, so we should create proper casts for them and detect them via casts. Then they can be located anywhere, don't conflict with RPCs, etc.

I would suggest to do the function interface first, and then extend later on.

[steve-chavez]

Am I right that currently it's only possible to say RETURNS "media/type" for functions using a domain media/type?
So we don't support any of the CAST idea, yet, but all the pieces are in place to be able to extend that with casts later on?

Yes (for aggregates too (#2825 (comment))) and yes.

I would suggest to do the function interface first, and then extend later on.

Agree, that sounds good.

[steve-chavez]

The answer to those questions in the second paragraph is: Casts. Those functions are effectively implementing a cast, so we should create proper casts for them and detect them via casts. Then they can be located anywhere, don't conflict with RPCs, etc.

Oh, thinking more about the above. Wouldn't the interface for media type handlers be simpler too if it's defined via CASTs?

So the example on https://postgrest.org/en/stable/references/api/media_type_handlers.html#handlers-for-tables-views would be:

create or replace function twkb_handler_transition (state bytea, next lines)
returns bytea as $$
  select state || st_astwkb(next.geom);
$$ language sql;

create or replace aggregate twkb_agg (lines) (
  initcond = ''
, stype = bytea
, sfunc = twkb_handler_transition
);

CREATE OR REPLACE FUNCTION twkb(lines) RETURNS "application/vnd.twkb" AS $$
  select twkb_agg($1);
$$ LANGUAGE SQL IMMUTABLE;

CREATE CAST (lines AS "application/vnd.twkb") WITH FUNCTION twkb(lines) AS IMPLICIT;

That way the aggregate is an internal detail and the media type is not at all involved in its definition. I believe it's much more clear despite having to define one more function too.

@wolfgangwalther WDYT? This would be a breaking change on current media type handlers, but not for functions only for tables (which doesn't seem to be used that much yet).

[steve-chavez]

Damn, the above makes sense but the current query doesn't work:

WITH pgrst_source AS ( SELECT "test"."lines".* FROM "test"."lines")
SELECT
    null::bigint AS total_result_set,
    pg_catalog.count(_postgrest_t) AS page_total,
    twkb(_postgrest_t::"test"."lines") AS body,
    nullif(current_setting('response.headers', true), '') AS response_headers,
    nullif(current_setting('response.status', true), '') AS response_status,
    '' AS response_inserted
FROM ( SELECT * FROM pgrst_source ) _postgrest_t;

ERROR:  column "_postgrest_t.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5:     twkb(_postgrest_t::"test"."lines") AS body,

And an aggregate cannot be used as cast function:

CREATE CAST (lines AS "application/vnd.twkb") WITH FUNCTION twkb_agg(lines) AS IMPLICIT;

ERROR:  cast function must be a normal function