Hello Daniel,
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.
Patch applies cleanly and compiles. "make check" ok, although there is
no specific test for this feature...
The documentation should mention the other CSV options (COPY, \copy, ...)
and explain how they compare to this one. Maybe a specific paragraph about
how to do CSV? I understand "\pset format csv" as triggering that all
outputs compared to per command options.
Given that this is somehow already available, I'm wondering why there is
no code sharing.
I find it annoying that setting csv keeps the default '|' separator, where
ISTM that it should be by default "," (as in COMMA separated value:-).
However it would not be a good idea to change another variables when setting
one, obviously.
Maybe some \csv command could set the format to csv, fieldsep to ",",
tuples_only to on, recordsep to '\n'? Not sure whether it would be
acceptable, though, and how to turn it off once turned on... Probably an
average (aka not good) idea:-)
The format adds column headers, however they are not escaped properly:
psql> \pset format csv
psql> \pset fieldsep ,
psql> SELECT 1 AS "hello, world", 2 AS """";
hello, world,"
1,2
Also it does not seem to work properly in expanded mode, both for the
column and values:
psql> \x
psql> SELECT 1 AS "bla""", E'\n,"' AS foo;
bla",1
foo,
,"
There MUST be some tests, especially with ugly stuff (escapes, newlines,
double quotes, various types, expanded or not, field seps, strange
column names...).
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.
* \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.
--
Fabien.
