Fabien COELHO wrote:
> 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:-)
Thanks for reviewing this patch!
Attached is a v2 fixing the bugs you mentioned, and adding ---csv/-C
as discussed upthread. I'll add some regression tests shortly.
About the default separator, the approach taken by this patch is that
the csv output is equivalent to unaligned with csv quoting, so it has
the same behavior and follows the options of the unaligned mode
as much as possible.
That seemed like a good idea to me when I wrote it, but maybe
a better idea would to have a new \pset fieldsep_csv
parameter with its own command-line option, and ignore {fieldsep,
fieldsep_zero, recordsep, recordsep_zero} for this format, just like
they are being ignored for HTML, or latex, or asciidoc...
Does anyone who think that csv should be added care
about this "alignment" with unaligned? :)
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index bfdf859..5224a4f 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -246,7 +246,7 @@ EOF
<listitem>
<para>
Use <replaceable class="parameter">separator</replaceable> as the
- field separator for unaligned output. This is equivalent to
+ field separator for unaligned and csv outputs. This is equivalent to
<command>\pset fieldsep</command> or <command>\f</command>.
</para>
</listitem>
@@ -382,7 +382,7 @@ EOF
<listitem>
<para>
Use <replaceable class="parameter">separator</replaceable> as the
- record separator for unaligned output. This is equivalent to
+ record separator for unaligned and csv outputs. This is equivalent to
<command>\pset recordsep</command>.
</para>
</listitem>
@@ -557,8 +557,8 @@ EOF
<term><option>--field-separator-zero</option></term>
<listitem>
<para>
- Set the field separator for unaligned output to a zero byte. This is
- equvalent to <command>\pset fieldsep_zero</command>.
+ Set the field separator for unaligned and csv outputs to a zero byte.
+ This is equivalent to <command>\pset fieldsep_zero</command>.
</para>
</listitem>
</varlistentry>
@@ -568,8 +568,9 @@ EOF
<term><option>--record-separator-zero</option></term>
<listitem>
<para>
- Set the record separator for unaligned output to a zero byte. This is
- useful for interfacing, for example, with <literal>xargs -0</literal>.
+ Set the record separator for unaligned and csv outputs to a zero byte.
+ This is useful for interfacing, for example, with
+ <literal>xargs -0</literal>.
This is equivalent to <command>\pset recordsep_zero</command>.
</para>
</listitem>
@@ -1937,9 +1938,9 @@ Tue Oct 26 21:40:57 CEST 1999
<listitem>
<para>
- Sets the field separator for unaligned query output. The default
- is the vertical bar (<literal>|</literal>). It is equivalent to
- <command>\pset fieldsep</command>.
+ Sets the field separator for unaligned and csv query outputs. The
+ default is the vertical bar (<literal>|</literal>). It is equivalent
+ to <command>\pset fieldsep</command>.
</para>
</listitem>
</varlistentry>
@@ -2546,8 +2547,8 @@ lo_import 152801
<term><literal>fieldsep</literal></term>
<listitem>
<para>
- Specifies the field separator to be used in unaligned output
- format. That way one can create, for example, tab- or
+ Specifies the field separator to be used in unaligned and csv output
+ formats. That way one can create, for example, tab- or
comma-separated output, which other programs might prefer. To
set a tab as field separator, type <literal>\pset fieldsep
'\t'</literal>. The default field separator is
@@ -2560,8 +2561,8 @@ lo_import 152801
<term><literal>fieldsep_zero</literal></term>
<listitem>
<para>
- Sets the field separator to use in unaligned output format to a zero
- byte.
+ Sets the field separator to use in unaligned or csv output formats to
+ a zero byte.
</para>
</listitem>
</varlistentry>
@@ -2584,9 +2585,13 @@ lo_import 152801
<term><literal>format</literal></term>
<listitem>
<para>
- Sets the output format to one of <literal>unaligned</literal>,
- <literal>aligned</literal>, <literal>wrapped</literal>,
- <literal>html</literal>, <literal>asciidoc</literal>,
+ Sets the output format to one of
+ <literal>unaligned</literal>,
+ <literal>aligned</literal>,
+ <literal>csv</literal>,
+ <literal>wrapped</literal>,
+ <literal>html</literal>,
+ <literal>asciidoc</literal>,
<literal>latex</literal> (uses <literal>tabular</literal>),
<literal>latex-longtable</literal>, or
<literal>troff-ms</literal>.
@@ -2601,6 +2606,12 @@ lo_import 152801
format).
</para>
+ <para><literal>csv</literal> format is similar to
+ <literal>unaligned</literal>, except that column contents are
+ enclosed in double quotes and quoted when necessary according to the
+ rules of the CSV format, and that no title or footer are printed.
+ </para>
+
<para><literal>aligned</literal> format is the standard,
human-readable,
nicely formatted text output; this is the default.
</para>
@@ -2747,8 +2758,8 @@ lo_import 152801
<term><literal>recordsep</literal></term>
<listitem>
<para>
- Specifies the record (line) separator to use in unaligned
- output format. The default is a newline character.
+ Specifies the record (line) separator to use in unaligned or
+ csv output formats. The default is a newline character.
</para>
</listitem>
</varlistentry>
@@ -2757,8 +2768,8 @@ lo_import 152801
<term><literal>recordsep_zero</literal></term>
<listitem>
<para>
- Sets the record separator to use in unaligned output format to a zero
- byte.
+ Sets the record separator to use in unaligned or csv output
+ formats to a zero byte.
</para>
</listitem>
</varlistentry>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 3560318..24f5a11 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3603,6 +3603,9 @@ _align2string(enum printFormat in)
case PRINT_TROFF_MS:
return "troff-ms";
break;
+ case PRINT_CSV:
+ return "csv";
+ break;
}
return "unknown";
}
@@ -3674,9 +3677,11 @@ do_pset(const char *param, const char *value,
printQueryOpt *popt, bool quiet)
popt->topt.format = PRINT_LATEX_LONGTABLE;
else if (pg_strncasecmp("troff-ms", value, vallen) == 0)
popt->topt.format = PRINT_TROFF_MS;
+ else if (pg_strncasecmp("csv", value, vallen) == 0)
+ popt->topt.format = PRINT_CSV;
else
{
- psql_error("\\pset: allowed formats are unaligned,
aligned, wrapped, html, asciidoc, latex, latex-longtable, troff-ms\n");
+ psql_error("\\pset: allowed formats are unaligned,
aligned, csv, wrapped, html, asciidoc, latex, latex-longtable, troff-ms\n");
return false;
}
}
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index be57574..2f17bcb 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -436,6 +436,7 @@ parse_psql_options(int argc, char *argv[], struct
adhoc_opts *options)
{"echo-all", no_argument, NULL, 'a'},
{"no-align", no_argument, NULL, 'A'},
{"command", required_argument, NULL, 'c'},
+ {"csv", no_argument, NULL, 'C'},
{"dbname", required_argument, NULL, 'd'},
{"echo-queries", no_argument, NULL, 'e'},
{"echo-errors", no_argument, NULL, 'b'},
@@ -476,7 +477,7 @@ parse_psql_options(int argc, char *argv[], struct
adhoc_opts *options)
memset(options, 0, sizeof *options);
- while ((c = getopt_long(argc, argv,
"aAbc:d:eEf:F:h:HlL:no:p:P:qR:sStT:U:v:VwWxXz?01",
+ while ((c = getopt_long(argc, argv,
"aAbc:Cd:eEf:F:h:HlL:no:p:P:qR:sStT:U:v:VwWxXz?01",
long_options,
&optindex)) != -1)
{
switch (c)
@@ -500,6 +501,9 @@ parse_psql_options(int argc, char *argv[], struct
adhoc_opts *options)
ACT_SINGLE_QUERY,
optarg);
break;
+ case 'C':
+ pset.popt.topt.format = PRINT_CSV;
+ break;
case 'd':
options->dbname = pg_strdup(optarg);
break;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8bc4a19..05171a9 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3603,8 +3603,8 @@ psql_completion(const char *text, int start, int end)
if (TailMatchesCS1("format"))
{
static const char *const my_list[] =
- {"unaligned", "aligned", "wrapped", "html", "asciidoc",
- "latex", "latex-longtable", "troff-ms", NULL};
+ {"unaligned", "aligned", "csv", "wrapped", "html",
"asciidoc",
+ "latex", "latex-longtable", "troff-ms", NULL};
COMPLETE_WITH_LIST_CS(my_list);
}
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index ec5ad45..c53082b 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -2783,6 +2783,132 @@ print_troff_ms_vertical(const printTableContent *cont,
FILE *fout)
}
}
+/*************************/
+/* CSV */
+/*************************/
+static void
+csv_escaped_print(const char *text, FILE *fout)
+{
+ const char *p;
+
+ fputc('"', fout);
+ for (p = text; *p; p++)
+ {
+ if (*p == '"')
+ fputc('"', fout); /* double quotes are doubled */
+ fputc(*p, fout);
+ }
+ fputc('"', fout);
+}
+
+static void
+csv_print_field(const char *text, FILE *fout, const char *sep)
+{
+ /*
+ * Enclose and escape field contents when one of these conditions is
+ * met:
+ * - the field separator is found in the contents
+ * - the field contains a CR or LF
+ * - the field contains a double quote
+ */
+ if ((sep != NULL && *sep != '\0' && strstr(text, sep) != NULL) ||
+ strcspn(text, "\r\n\"") != strlen(text))
+ {
+ csv_escaped_print(text, fout);
+ }
+ else
+ fputs(text, fout);
+}
+
+static void
+print_csv_text(const printTableContent *cont, FILE *fout)
+{
+ const char *const *ptr;
+ bool need_recordsep = false;
+ int i;
+
+ if (cancel_pressed)
+ return;
+
+ /*
+ * The title and footer are never printed in csv format.
+ * The header is printed if opt_tuples_only is false.
+ */
+
+ if (cont->opt->start_table && !cont->opt->tuples_only)
+ {
+ /* print headers */
+ for (ptr = cont->headers; *ptr; ptr++)
+ {
+ if (ptr != cont->headers)
+ print_separator(cont->opt->fieldSep, fout);
+ csv_print_field(*ptr, fout,
cont->opt->fieldSep.separator);
+ }
+ need_recordsep = true;
+ }
+
+ /* print cells */
+ for (i = 0, ptr = cont->cells; *ptr; i++, ptr++)
+ {
+ if (need_recordsep)
+ {
+ print_separator(cont->opt->recordSep, fout);
+ need_recordsep = false;
+ if (cancel_pressed)
+ break;
+ }
+
+ csv_print_field(*ptr, fout, cont->opt->fieldSep.separator);
+
+ if ((i + 1) % cont->ncolumns)
+ print_separator(cont->opt->fieldSep, fout);
+ else
+ need_recordsep = true;
+ }
+
+ if (cont->opt->stop_table && need_recordsep)
+ {
+ print_separator(cont->opt->recordSep, fout);
+ }
+}
+
+static void
+print_csv_vertical(const printTableContent *cont, FILE *fout)
+{
+ unsigned int i;
+ const char *const *ptr;
+ bool need_recordsep = false;
+
+ if (cancel_pressed)
+ return;
+
+ /* print records */
+ for (i = 0, ptr = cont->cells; *ptr; i++, ptr++)
+ {
+ if (need_recordsep)
+ {
+ /* record separator is 2 occurrences of recordsep in
this mode */
+ print_separator(cont->opt->recordSep, fout);
+ print_separator(cont->opt->recordSep, fout);
+ need_recordsep = false;
+ if (cancel_pressed)
+ break;
+ }
+
+ csv_print_field(cont->headers[i % cont->ncolumns], fout,
+ cont->opt->fieldSep.separator);
+
+ print_separator(cont->opt->fieldSep, fout);
+
+ csv_print_field(*ptr, fout, cont->opt->fieldSep.separator);
+
+ if ((i + 1) % cont->ncolumns)
+ print_separator(cont->opt->recordSep, fout);
+ else
+ need_recordsep = true;
+ }
+}
+
/********************************/
/* Public functions */
@@ -3234,6 +3360,12 @@ printTable(const printTableContent *cont,
else
print_aligned_text(cont, fout, is_pager);
break;
+ case PRINT_CSV:
+ if (cont->opt->expanded == 1)
+ print_csv_vertical(cont, fout);
+ else
+ print_csv_text(cont, fout);
+ break;
case PRINT_HTML:
if (cont->opt->expanded == 1)
print_html_vertical(cont, fout);
diff --git a/src/include/fe_utils/print.h b/src/include/fe_utils/print.h
index 83320d0..82e50fb 100644
--- a/src/include/fe_utils/print.h
+++ b/src/include/fe_utils/print.h
@@ -33,7 +33,8 @@ enum printFormat
PRINT_ASCIIDOC,
PRINT_LATEX,
PRINT_LATEX_LONGTABLE,
- PRINT_TROFF_MS
+ PRINT_TROFF_MS,
+ PRINT_CSV
/* add your favourite output format here ... */
};
