Using select + text/csv output results in wrong csv header

[bjornharrtell]

When using select query string param to reorder and/or omit columns and outpout format text/csv the csv column names header will still be as without select (but data is selected as expected).

[laurenceisla]

An extra example would be great to further clarify the issue. The request and the expected/wrong responses would be enough, e.g.:

The request:

curl 'localhost:3000/table?select=a,b' -H "Accept: text/csv"

Expected:

expected,csv

Got:

bad,csv

[wolfgangwalther]

Against our spec fixtures:

A test for regular views/tables:

    it "should respond with CSV with custom select" $
      request methodGet "/projects?select=name,id,client_id&id=eq.2"
              (acceptHdrs "text/csv") ""
        `shouldRespondWith`
        [str|name,id,client_id
            |"Windows 10",2,1|]
        { matchStatus  = 200
        , matchHeaders = ["Content-Type" <:> "text/csv; charset=utf-8"]
        }

A test for RPCs:

      it "returns CSV with custom select" $
        request methodGet "/rpc/getproject?select=name,id,client_id&id=2"
                (acceptHdrs "text/csv") ""
          `shouldRespondWith`
          [str|name,id,client_id
              |"Windows 10",2,1|]
          { matchStatus  = 200
          , matchHeaders = ["Content-Type" <:> "text/csv; charset=utf-8"]
          }

The first test passes, the second test fails:

  1) Feature.Query.RpcSpec, remote procedure call, a proc that returns a set, returns CSV with custom select
       body mismatch:
         expected: "name,id,client_id\n\"Windows 10\",2,1"
         but got:  "client_id,id,name\n\"Windows 10\",2,1"

To get the wrong output you need:

• call an RPC
• select columns in non-alphabetic order

The values will be returned in the correct order, but the headers will always be sorted alphabetically.

[bjornharrtell]

Thank you @wolfgangwalther, this is precisely the issue.

[pebosi]

Having the same problem, additionally when using an rpc i cant filter for fields not in the select. I wanted to build an csv out of an filtered rpc, but i only want to include cols which are not part of a filter.

[steve-chavez]

I've started work on pg_csv, so far results are very good:

• It proves this issue is solved, see https://github.com/PostgREST/pg_csv/blob/master/test/expected/postgrest.out
• It outperforms our current CSV query, almost x2 the TPS
  • I've loadtested this with pgbench and a wide table
  • Results are proven by CI, see summary on postgrest loadtest. tldr: postgrest tps = 85.057872 vs csv_agg tps = 169.796267