Generating CSV file upon GET request with appropriate headers

[diogob]

I will gladly help to implement this one and I'd like to know if it's a desirable feature.

My use case is: I want to make some query results available as a CSV for my users, so that they can use this data in spreadsheets.

Possible solutions: I could bring the data in JSON format from PostgREST and assemble a CSV in the browser. But it seems that we could benefit from PostgreSQL amazing performance when executing COPY queries to generate a CSV in the backend. Besides the client-side solution impedes interoperability with other clients that might expect a CSV file.

My proposal: We use the content type provided in the request when executing a GET (this is also useful for a POST but stating with GET sounds easier). If the content type matches text/csv we use the same where clause generated for the JSON response but plug it in a COPY statement.

@begriffs I've noticed you've opened this issue: nikita-volkov/hasql#1 so I guess that there are changes in hasql-postgres needed before anyone can implement this in PostgREST right?

[begriffs]

This is an excellent feature, I'd love your help with it. Your approach of using the Accept header in the request sounds perfect.

The issue in Hasql with copy mode came up when I was trying to do bulk inserts. Bulk reads may or may not be easier. For the inserts I ultimately made a workaround by using an insert command with multiple values (suboptimal because not as fast as copy).

[signul]

I would also like this feature with a slight difference. I would rather not have users download CSV files and import them. Instead setting content type to text/csv and returning comma separated rows instead of JSON. This way I can call the services from excel and return directly into a workbook utilizing excel's background refresh capabilities.

[diogob]

@begriffs unfortunately I've been trying to implement COPY support in hasql-postgres without success. So I was wondering if you would be willing to integrate a PR with this feature using a SELECT statement.