Media Type Handlers

Media Type Handlers allow PostgREST to deliver custom media types. These handlers extend the builtin ones and can also override them.

Media types are expressed as type aliases using domains and their name must comply to RFC 6838 requirements.

CREATE DOMAIN "application/json" AS json;

Using these domains, functions can become handlers and user-defined aggregates can serve as handlers for Tables and Views and Table-Valued Functions.

Important

• PostgREST vendor media types (application/vnd.pgrst.plan, application/vnd.pgrst.object and application/vnd.pgrst.array) cannot be overriden.

• Long media types like application/vnd.openxmlformats-officedocument.wordprocessingml.document cannot be expressed as domains since they surpass PostgreSQL identifier length. For these you can use the The “Any” Handler.

Handler Function

As an example, let’s obtain the TWKB compressed binary format for a PostGIS geometry.

create extension postgis;

create table lines (
  id   int primary key
, name text
, geom geometry(LINESTRING, 4326)
);

insert into lines values (1, 'line-1', 'LINESTRING(1 1,5 5)'::geometry), (2, 'line-2', 'LINESTRING(2 2,6 6)'::geometry);

For this you can create a vendor media type.

create domain "application/vnd.twkb" as bytea;

And use it as a return type on a function, to make it a handler.

create or replace function get_line (id int)
returns "application/vnd.twkb" as $$
  select st_astwkb(geom) from lines where id = get_line.id;
$$ language sql;

Note

For PostgreSQL <= 12, you’ll need a cast on the function body st_astwkb(geom)::"application/vnd.twkb".

Now you can request the TWKB output like so:

curl 'localhost:3000/rpc/get_line?id=1' -i \
  -H "Accept: application/vnd.twkb"

HTTP/1.1 200 OK
Content-Type: application/vnd.twkb

# binary output

Note that PostgREST will automatically set the Content-Type to application/vnd.twkb.

Handlers for Tables/Views

To benefit from a compressed format like TWKB, it makes more sense to obtain many rows instead of one. Let’s allow that by adding a handler for the table.

User-defined aggregates can be turned into handlers by using domain media types as the return type of their transition or final functions.

Let’s create a transition function for this example.

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

Now we’ll use it on a new aggregate defined for the lines table.

create or replace aggregate twkb_agg (lines) (
  initcond = ''
, stype = "application/vnd.twkb"
, sfunc = twkb_handler_transition
);

Note

You can test see this aggregate working with:

SELECT twkb_agg(l) from lines l;

                           twkb_agg
---------------------------------------------------------------
\xa20002c09a0cc09a0c80ea3080ea30a2000280b51880b51880ea3080ea30
(1 row)

Now you can request the table endpoint with the twkb media type:

curl 'localhost:3000/lines' -i \
  -H "Accept: application/vnd.twkb"

HTTP/1.1 200 OK
Content-Type: application/vnd.twkb

# binary output

If you have a table-valued function returning the same table type, the handler can also act upon on it.

create or replace function get_lines ()
returns setof lines as $$
  select * from lines;
$$ language sql;

curl 'localhost:3000/get_lines' -i \
  -H "Accept: application/vnd.twkb"

HTTP/1.1 200 OK
Content-Type: application/vnd.twkb

# binary output

Overriding a Builtin Handler

Let’s override the existing text/csv handler for the table to provide a more complex CSV output. It’ll include a Byte order mark (BOM) plus a Content-Disposition header to set a name for the downloaded file.

Create a domain for the standard text/csv media type.

create domain "text/csv" as text;

And a transition function that returns the domain.

create or replace function bom_csv_trans (state text, next lines)
returns "text/csv" as $$
  select state || next.id::text || ',' || next.name || ',' || next.geom::text || E'\n';
$$ language sql;

This time we’ll add a final function. This will add the CSV header, the BOM and the Content-Disposition header.

create or replace function bom_csv_final (data "text/csv")
returns "text/csv" as $$
  -- set the Content-Disposition header
  select set_config('response.headers', '[{"Content-Disposition": "attachment; filename=\"lines.csv\""}]', true);
  select
    -- EFBBBF is the BOM in UTF8 https://en.wikipedia.org/wiki/Byte_order_mark#UTF-8
    convert_from (decode (E'EFBBBF', 'hex'),'UTF8') ||
    -- the header for the CSV
    (E'id,name,geom\n' || data);
$$ language sql;

Now use the transition and final function as part of the new aggregate.

create or replace aggregate bom_csv_agg (lines) (
  initcond = ''
, stype = "text/csv"
, sfunc = bom_csv_trans
, finalfunc = bom_csv_final
);

Note

You can test this with:

select bom_csv_agg(l) from lines l;
                                             bom_csv_agg
-----------------------------------------------------------------------------------------------------
 id,name,geom                                                                                      +
 1,line-1,0102000020E610000002000000000000000000F03F000000000000F03F00000000000014400000000000001440+
 2,line-2,0102000020E6100000020000000000000000000040000000000000004000000000000018400000000000001840+

(1 row)

And request it like:

curl 'localhost:3000/lines' -i \
  -H "Accept: text/csv"

HTTP/1.1 200 OK
Content-Type: text/csv
Content-Disposition: attachment; filename="lines.csv"

id,name,geom
1,line-1,0102000020E610000002000000000000000000F03F000000000000F03F00000000000014400000000000001440
2,line-2,0102000020E6100000020000000000000000000040000000000000004000000000000018400000000000001840

The “Any” Handler

For more flexibility, you can also define a catch-all handler by using a domain named */* (any media type). This handler obeys the following rules:

• It responds to all media types and even to requests that don’t include an Accept header.

• It sets the Content-Type header to application/octet-stream by default, but this can be overridden inside the function with Response Headers.

• It overrides all other handlers (builtin or custom), so it’s better to do it for an isolated function or view.

Let’s define an any handler for a view that will always respond with XML output. It will accept text/xml, application/xml, */* and reject other media types.

create domain "*/*" as bytea;

-- we'll use an .xml suffix for the view to be clear its output is always XML
create view "lines.xml" as
select * from lines;

-- transition function
create or replace function lines_xml_trans (state "*/*", next "lines.xml")
returns "*/*" as $$
  select state || xmlelement(name line, xmlattributes(next.id as id, next.name as name), next.geom)::text::bytea || E'\n' ;
$$ language sql;

-- final function
create or replace function lines_xml_final (data "*/*")
returns "*/*" as $$
declare
  -- get the Accept header
  req_accept text := current_setting('request.headers', true)::json->>'accept';
begin
  -- when we need to override the default Content-Type (application/octet-stream) set by PostgREST
  if req_accept = '*/*' then
    perform set_config('response.headers', json_build_array(json_build_object('Content-Type', 'text/xml'))::text, true);
  elsif req_accept IN ('application/xml', 'text/xml') then
    perform set_config('response.headers', json_build_array(json_build_object('Content-Type', req_accept))::text, true);
  else
    -- we'll reject other non XML media types, we need to reject manually since */* will command PostgREST to accept all media types
    raise sqlstate 'PT415' using message = 'Unsupported Media Type';
  end if;

  return data;
end; $$ language plpgsql;

-- new aggregate
create or replace aggregate lines_xml_agg ("lines.xml") (
  stype = "*/*"
, sfunc = lines_xml_trans
, finalfunc = lines_xml_final
);

Test it on SQL:

select (encode(lines_xml_agg(x), 'escape'))::xml from "lines.xml" x;
                                                            encode
------------------------------------------------------------------------------------------------------------------------------
 <line id="1" name="line-1">0102000020E610000002000000000000000000F03F000000000000F03F00000000000014400000000000001440</line>+
 <line id="2" name="line-2">0102000020E6100000020000000000000000000040000000000000004000000000000018400000000000001840</line>+

Now we can omit the Accept header and it will respond with XML.

curl 'localhost:3000/lines.xml' -i

HTTP/1.1 200 OK
Content-Type: text/xml

<line id="1" name="line-1">0102000020E610000002000000000000000000F03F000000000000F03F00000000000014400000000000001440</line>
<line id="2" name="line-2">0102000020E6100000020000000000000000000040000000000000004000000000000018400000000000001840</line>

And it will accept only XML media types.

curl 'localhost:3000/lines.xml' -i \
  -H "Accept: text/xml"

HTTP/1.1 200 OK
Content-Type: text/xml

curl 'localhost:3000/lines.xml' -i  \
  -H "Accept: application/xml"

HTTP/1.1 200 OK
Content-Type: text/xml

curl 'localhost:3000/lines.xml' -i \
  -H "Accept: unknown/media"

HTTP/1.1 415 Unsupported Media Type