Re: csv format for psql - Mailing list pgsql-hackers

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.