CSV output issues

[bwbroersma]

SQL output (and input) is wrong in multiple ways.

Main issue on the output side:

• the header is constructed by row_to_json->json_object_keys->string_agg without a " added or doubled (as escape), see the code and [1]

  postgrest/src/PostgREST/QueryBuilder/Private.hs

  Lines 108 to 116 in d575852

  	asCsvHeaderF =
  	"(SELECT coalesce(string_agg(a.k, ','), '')" <>
  	" FROM (" <>
  	" SELECT json_object_keys(r)::TEXT as k" <>
  	" FROM ( " <>
  	" SELECT row_to_json(hh) as r from " <> sourceCTEName <> " as hh limit 1" <>
  	" ) s" <>
  	" ) a" <>
  	")"

• the body is constructed by the hacky substring(2,composite_type::text,length-2), but the composite output != CSV output, since \ is escaped to \\ see the code and [2]

  postgrest/src/PostgREST/QueryBuilder/Private.hs

  Line 117 in d575852

  asCsvBodyF = "coalesce(string_agg(substring(_postgrest_t::text, 2, length(_postgrest_t::text) - 2), '\n'), '')"

Minor issues:

• changing CSV delimiter/separator is not impossible due to the use of the composite text cast hack, which has , hard-coded (https://github.com/postgres/postgres/blob/12afc7145c03c212f26fea3a99e016da6a1c919c/src/backend/utils/adt/rowtypes.c#L385)
• NULL-value output is handled nice, but Asymetric CSV encoding/decoding #1043 states the input is not (not verified)
• optional BOM flag would be great for compatibility Support Accept-Charset for international characters #1371
• unwrapping embedded data Bad csv output on queries with embeds #1001

Possible (body) solution start:

SELECT (
  SELECT array_to_string(array_agg('"' || REPLACE(val,'"','""') || '"'), ',')
  	FROM unnest(arr) val
  )
  FROM (
    SELECT ARRAY[.. columns ..]
      FROM ..
  ) alias(arr);

Probably faster then using json functions, but NULL output issues & change of composite to array will need a code change

Another try with row_to_json:

SELECT (
  SELECT string_agg(
    CASE
      WHEN json_typeof(val.value) = 'null' THEN ''
      ELSE '"' || REPLACE(val.value#>>'{}','"','""') || '"'
    END,
    ',')
    FROM
      json_each(row_to_json(row)) val
  )
  FROM (
    SELECT (.. composite ..)
      FROM ...
    ) alias(row);

This will change the fact that non-strings (booleans and numbers) will be quoted, which is not ideal for type detection, but can be fixed with an extra WHEN (if the delimiter/separator is not in the ::text value of course). Also this does not yet fix the resource embedding.

[1]: See the PostgreSQL Identifiers syntax

Quoted identifiers can contain any character, except the character with code zero.

[2]: See the code record_out code or PostgreSQL Docs Composite Type Input and Output Syntax

[steve-chavez]

Definitely a lot of shortcomings with CSV right now. Ideally we would solve all of these with pg COPY in a more elegant(it already support things like custom delimiters, null strings, etc) and performant way, but we're missing COPY support in our lib nikita-volkov/hasql#1.

[bwbroersma]

Ah, I was wrongfully ignoring COPY .. TO .. because I thought it had to be a file output, but STDOUT can be used too of course. So indeed COPY .. TO STDOUT would be best.

The stalling of basic PostgreSQL features in hasql (namely notifications nikita-volkov/hasql#43 for a DDL notify and nikita-volkov/hasql#1 COPY) are holding progress in PostgREST. Same goes for the lack of OpenAPI 3.0 that is an upstream problem GetShopTV/swagger2#105 (comment) (although there is a v3 version, but it looks unmaintained). These issues / PRs are open for years, I'm afraid we have to do some work upstream to fix these issues.

[steve-chavez]

Regarding OpenAPI, we have the alternative of generating the spec with SQL.

I made this proposal here #790 (comment), and it was added in #1317 (root-spec config still not documented)

Then we could test the spec with this pg extension https://github.com/gavinwahl/postgres-json-schema (we're already using this for testing postgrest openapi spec).

[bwbroersma]

Ok wow, this is really powerful! Thanks for pointing me to it. Kind of removes the need for notify too, since we can use a live view during development (and optionally use ddl_command_end trigger to refresh a materialized view to speed it up).

Next thing is using the OpenAPI or more likely a PostgREST schema endpoint for internal use, this way authentication can also be blocked before trying to execute, and give more meaningful errors.

Off topic: I also completely agree with the fact that SwaggerUI is overrated, ReDoc seems much cleaner (and has a generate static HTML) but lacks 'Try it'. But the best thing would be to create a custom front end tool for PostgREST to show the API, to handle all input operators and enum arrays, etc., handle the embedding, etc.

[ppKrauss]

See reference about CSV conventions and how to express alternatives by JSON (CSV schema metadata),
https://frictionlessdata.io/specs/csv-dialect/

[wolfgangwalther]

Definitely a lot of shortcomings with CSV right now. Ideally we would solve all of these with pg COPY in a more elegant(it already support things like custom delimiters, null strings, etc) and performant way, but we're missing COPY support in our lib nikita-volkov/hasql#1.

Even if we had COPY support in hasql, we still couldn't really use it for text/csv output - because the json output is only one of several columns we return right now. We don't want our whole query to return CSV - we only want the body in one of the columns to contain CSV.

[wolfgangwalther]

There's one more problem with csv headers when returning a composite result from an RPC right now:

    it "should respond with correct, aliased CSV headers to 'text/csv' request on RPC" $
      request methodGet "/rpc/getallprojects?select=client:client_id,name,project:id&limit=2&order=id"
              (acceptHdrs "text/csv") ""
        `shouldRespondWith` "client,project,name\n1,1,Windows 7\n1,2,Windows 10"
        { matchStatus  = 200
        , matchHeaders = ["Content-Type" <:> "text/csv; charset=utf-8"]
        }

returns

       body mismatch:
         expected: "client,project,name\n1,1,Windows 7\n1,2,Windows 10"
         but got:  "client_id,id,name\n1,\"Windows 7\",1\n1,\"Windows 10\",2"

The aliases don't work and the headers are returned in the wrong order. This is because asCsvF selects the headers from sourceCTEName and the data from _postgrest_t (where the renaming and reordering happens in this case).

I'm hoping for #1582 to allow defining a csv aggregate. Unfortunately, #2701 won't help, yet, because I need to return json or csv from the same RPC dynamically depending on accept header.