Postgrest: Need for CSV of a JSON

[ppKrauss]

• PostgreSQL version: 12+
• PostgREST version: 7+
• Operating system: UBUNTU

New issue: an /rpc_csv/ or HTTP signal that says to PostgREST to convert full JSON array into table when Accept 'text/csv'.

---

Suppose

CREATE or replace FUNCTION api.uri_dispatcher(uri text DEFAULT '') RETURNS JSONb AS $f$
  SELECT '[{"id":1,"test":"t1"},{"id":2,"test":"t2"}]'::jsonb
$f$ language SQL immutable;

Suppose also sudo docker kill -s SIGUSR1 container after function created.

... And suppose NGINX configs with something as:

    # is working fine: 
    location /_sql.csv/ {
      proxy_set_header Accept 'text/csv';
      proxy_pass http://localhost:3000/;
    }
    location /_sql.json/ {
      proxy_pass http://localhost:3000/;
    }

    # not is working in the CSV case:
    location /_rpc.csv/ {
      proxy_set_header Accept 'text/csv';
      proxy_pass http://localhost:3000/rpc/;
    }
    location /_rpc.json/ {
      proxy_pass http://localhost:3000/rpc/;
    }

Where we need same behaviour as SQL tables, that is, need a real CSV, not a "CSV with JSON content". Examples:

• _sql.json/t is a "JSON table" and _sql.csv/t is a "CSV table".
• _rpc.json/uri_dispatcher is a "JSON table" and _rpc.csv/uri_dispatcher is not a "CSV table".

---

PS: please check also this bug in the SETOF JSON.

[wolfgangwalther]

We currently create the CSV directly from the records of the query, not from an intermediate json output:

https://github.com/PostgREST/postgrest/blob/0d5520d91d6fcb895c209dec3329406d027d7736/src/PostgREST/Private/QueryFragment.hs#L81

I suppose you return record types from your RPCs instead of a single JSON column. Those record types would properly be translated to CSV.

[wolfgangwalther]

I thought a bit more about this, and I don't think we should support this.

In general we translate a result-set of multiple columns and rows into an array of objects for json or into a csv table. Only when we return a json document, there are a few special cases, because this is valid json as well:

• a single row, multiple columns will be just a json object, without the array (currently through accept header)
• multiple rows of a scalar value will just be a json array with scalars (not implemented yet, but in a PR)
• a single scalar value will just be a json scalar value

We do a minimal special treatment for the last case, because return strings as proper json would mean we'd have to surround them in quotes. We don't do that. We just return the plain value.

A function that returns JSON or JSONB is of the last variant: the return value is just a scalar value and is returned plain. If you return a json object or array this looks like what we return with accept=json - but it's not. Just think about this: A function returning JSON can also return 1 or "hello world" - it does not need to return an object or an array of objects.

While the json format can have different shapes, the CSV format can not. CSV is a table. A table is defined by rows and columns. There are no special representations for tables. So whenever CSV is requested, we return a table. In the case of a scalar function thats a table with 1 row and 1 column. In this 1 cell, we dump the plain value - just like we expect it with every other case. Only that in the case of json objects and arrays, this is ugly, because we have to escape it. But I don't see a way around that.

We are not doing any transformations between csv or json, nor should we. We are only translating a query result set into either json or csv.

Where we need same behaviour as SQL tables, that is, need a real CSV, not a "CSV with JSON content".

We have the same behaviour in both RPCs and tables. Try requesting a table with one JSON column as CSV and you will get exactly the same kind of output. This is what you're returning with the RPC.

[wolfgangwalther]

As for a possible solution to your use-case: When you need to return csv from a function like this and can't return SETOF <recordtype>, because the shape of the record might be different - then you need to do the conversion to csv inside the RPC and use RETURNS TEXT. This will be a scalar value and again just be dumped as-is. Currently, you'd have to fiddle a bit with the accept header, because you would need to request it as either json or application/octet-stream to get what you want. But with #1582 it should be possible to tell postgrest, that you're handling text/csv on your own.