Hi,
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.
* \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.
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 7ea7edc..ebb3d35 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>
@@ -376,7 +376,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>
@@ -551,8 +551,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>
@@ -562,8 +562,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>
@@ -1918,9 +1919,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>
@@ -2527,8 +2528,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
@@ -2541,8 +2542,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>
@@ -2565,9 +2566,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>.
@@ -2582,6 +2587,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>
@@ -2728,8 +2739,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>
@@ -2738,8 +2749,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/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..45baa52 100644
--- a/src/fe_utils/print.c
+++ b/src/fe_utils/print.c
@@ -2783,6 +2783,125 @@ 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
+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);
+ fputs(*ptr, fout);
+ }
+ 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;
+ }
+
+ /*
+ * 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 ((cont->opt->fieldSep.separator != NULL &&
+ *cont->opt->fieldSep.separator != '\0' &&
+ strstr(*ptr, cont->opt->fieldSep.separator) !=
NULL) ||
+ strcspn(*ptr, "\r\n\"") != strlen(*ptr))
+ {
+ csv_escaped_print(*ptr, fout);
+ }
+ else
+ fputs(*ptr, fout);
+
+ 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;
+ }
+
+ fputs(cont->headers[i % cont->ncolumns], fout);
+ print_separator(cont->opt->fieldSep, fout);
+ fputs(*ptr, fout);
+
+ if ((i + 1) % cont->ncolumns)
+ print_separator(cont->opt->recordSep, fout);
+ else
+ need_recordsep = true;
+ }
+}
+
/********************************/
/* Public functions */
@@ -3234,6 +3353,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 ... */
};
