This patch implements csv as an output format in psql (\pset format csv). It's quite similar to the unaligned format, except that it applies CSV quoting rules (obviously!) and that it prints no footer and no title. As with unaligned, a header with column names is output unless tuples_only is on. It also supports the fieldsep/fielsep_zero and recordsep/recordsep_zero settings.
Most of times, the need for CSV is covered by \copy or COPY with the CSV option, but there are some cases where it would be more practical to have it as an output format in psql.
I absolutely agree
* \copy does not interpolate psql variables and is a single-line command, so making a query fit these contraints can be cumbersome. It can be got around by defining a temporary view and \copy from that view, but that doesn't work in a read-only context such as when connected to a standby.
* the server-side COPY TO STDOUT can also be used from psql, typically with psql -c "COPY (query) TO STDOUT CSV" > file.csv, but that's too simple to extract multiple result sets per script. COPY is also more rigid than psql in the options to delimit fields and records.
* copy with csv can't help for the output of meta-commands such as \gx, \crosstabview, \l, \d ... whereas a CSV format within psql does work with these.
It is great - long time I miss this feature - It is interesting for scripting, ETL, ..
This format is too important, so some special short or long option can be practical (it will be printed in help)
some like --csv
I found one issue - PostgreSQL default field separator is "|". Maybe good time to use more common "," ?
Or when field separator was not explicitly defined, then use "," for CSV, and "|" for other. Although it can be little bit messy
Thank you
Pavel
Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite