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

From Tom Lane
Subject Re: csv format for psql
Date
Msg-id 32765.1543195607@sss.pgh.pa.us
Whole thread Raw
In response to Re: csv format for psql  (Fabien COELHO <coelho@cri.ensmp.fr>)
Responses Re: csv format for psql
Re: csv format for psql
Re: csv format for psql
Re: csv format for psql
List pgsql-hackers
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> Basically the proposed patch addresses a simple and convenient use case 
> which are neither addressed by \copy nor COPY. The fact that more options 
> are available with these commands does it precludes its usefulness as is.

Yeah, I agree that this option is useful independently of whether COPY
provides something similar.  I think the killer argument is that right
now, psql-ref.sgml repeatedly suggests that unaligned mode with fieldsep
',' is a reasonable way to produce comma-separated output for consumption
by other programs.  That's like handing our users a loaded foot-gun.
And, in fact, right now *none* of psql's table output formats is both
unambiguous and reasonably simple/popular to use.  So the astonishing
thing about this patch, IMO, is that we didn't do it a decade ago.

I went through the documentation to improve that point, and did some other
cosmetic cleanup including rebasing up to HEAD, and got the attached.

I think there are two remaining points to settle:

1. Are we limiting the separator to be a single-byte character or not?
If we are, the code could be made simpler and faster by working with a
"char" value instead of a string.  If we're not, then Michael's change
needs to be undone (but I didn't do that here).

I feel that if we allow multi-byte characters here, we might as well
take the training wheels off and just say you can use any separator
string you want, as long as it doesn't contain double quote, \r, or \n.
Most programs reading a file are not going to perceive a difference
between separating fields with a single multibyte character and multiple
single-byte characters; either they can cope or they can't.  A fair
number of them are going to be in the latter category.  So we can either
say "do what you wish, it's your problem whether anything can read the
result" or "we're going to restrict you to something that (perhaps) is
more widely readable".  I'm a bit inclined to the former viewpoint.
If we were in the business of being restrictive, why would we allow
the field separator to be changed at all?  The name of the format is
*comma* separated values, not something else.

2. Speaking of the field separator, I'm pretty desperately unhappy
with the choice of "fieldsep_csv" as the parameter name.  The trouble
with that is that it encourages sticking "fieldsep_csv" in between
"fieldsep" and "fieldsep_zero", because alphabet.  But "fieldsep" and
"fieldsep_zero" are *intimately* tied together, in fact it's only a
dubious implementation choice that made them separate parameters at all.
It does not make any semantic sense to stick other vaguely-related
parameters in between, neither in the documentation nor in \pset output.

We could avoid this self-inflicted confusion by choosing a different
parameter name.  I'd be good with "csv_fieldsep" or "csvfieldsep".

Or we could kill both issues by hard-wiring the separator as ','.

Thoughts?

            regards, tom lane

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 6e6d0f4..d53451b 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** PostgreSQL documentation
*** 68,75 ****
        <listitem>
        <para>
        Switches to unaligned output mode. (The default output mode is
!       otherwise aligned.)  This is equivalent to <command>\pset format
!       unaligned</command>.
        </para>
        </listitem>
      </varlistentry>
--- 68,75 ----
        <listitem>
        <para>
        Switches to unaligned output mode. (The default output mode is
!       <literal>aligned</literal>.)  This is equivalent to
!       <command>\pset format unaligned</command>.
        </para>
        </listitem>
      </varlistentry>
*************** EOF
*** 152,157 ****
--- 152,167 ----
      </varlistentry>

      <varlistentry>
+       <term><option>--csv</option></term>
+       <listitem>
+       <para>
+       Switches to <acronym>CSV</acronym> (Comma-Separated Values) output
+       mode.  This is equivalent to <command>\pset format csv</command>.
+       </para>
+       </listitem>
+     </varlistentry>
+
+     <varlistentry>
        <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
        <term><option>--dbname=<replaceable class="parameter">dbname</replaceable></option></term>
        <listitem>
*************** EOF
*** 270,277 ****
        <term><option>--html</option></term>
        <listitem>
        <para>
!       Turn on <acronym>HTML</acronym> tabular output. This is
!       equivalent to <literal>\pset format html</literal> or the
        <command>\H</command> command.
        </para>
        </listitem>
--- 280,287 ----
        <term><option>--html</option></term>
        <listitem>
        <para>
!       Switches to <acronym>HTML</acronym> output mode.  This is
!       equivalent to <command>\pset format html</command> or the
        <command>\H</command> command.
        </para>
        </listitem>
*************** lo_import 152801
*** 2547,2554 ****
            <listitem>
            <para>
            Specifies the field separator to be used in unaligned output
!           format. 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
            <literal>'|'</literal> (a vertical bar).
--- 2557,2564 ----
            <listitem>
            <para>
            Specifies the field separator to be used in unaligned output
!           format. That way one can create, for example, tab-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
            <literal>'|'</literal> (a vertical bar).
*************** lo_import 152801
*** 2557,2562 ****
--- 2567,2585 ----
            </varlistentry>

            <varlistentry>
+           <term><literal>fieldsep_csv</literal></term>
+           <listitem>
+           <para>
+           Specifies the field separator to be used in
+           <acronym>CSV</acronym> output format.  If the separator character
+           appears in a field's value, that field is output inside double
+           quotes, following standard <acronym>CSV</acronym> rules.
+           The default is a comma.
+           </para>
+           </listitem>
+           </varlistentry>
+
+           <varlistentry>
            <term><literal>fieldsep_zero</literal></term>
            <listitem>
            <para>
*************** lo_import 152801
*** 2585,2606 ****
            <listitem>
            <para>
            Sets the output format to one of <literal>aligned</literal>,
!           <literal>asciidoc</literal>, <literal>html</literal>,
!           <literal>latex</literal> (uses <literal>tabular</literal>),
            <literal>latex-longtable</literal>, <literal>troff-ms</literal>,
            <literal>unaligned</literal>, or <literal>wrapped</literal>.
            Unique abbreviations are allowed.
            </para>

            <para><literal>unaligned</literal> format writes all columns of a row on one
            line, separated by the currently active field separator. This
            is useful for creating output that might be intended to be read
!           in by other programs (for example, tab-separated or comma-separated
!           format).
            </para>

!           <para><literal>aligned</literal> format is the standard, human-readable,
!           nicely formatted text output;  this is the default.
            </para>

            <para><literal>wrapped</literal> format is like <literal>aligned</literal> but wraps
--- 2608,2655 ----
            <listitem>
            <para>
            Sets the output format to one of <literal>aligned</literal>,
!           <literal>asciidoc</literal>,
!           <literal>csv</literal>,
!           <literal>html</literal>,
!           <literal>latex</literal>,
            <literal>latex-longtable</literal>, <literal>troff-ms</literal>,
            <literal>unaligned</literal>, or <literal>wrapped</literal>.
            Unique abbreviations are allowed.
            </para>

+           <para><literal>aligned</literal> format is the standard,
+           human-readable, nicely formatted text output; this is the default.
+           </para>
+
            <para><literal>unaligned</literal> format writes all columns of a row on one
            line, separated by the currently active field separator. This
            is useful for creating output that might be intended to be read
!           in by other programs, for example, tab-separated or comma-separated
!           format.  However, the field separator character is not treated
!           specially if it appears in a column's value;
!           so <acronym>CSV</acronym> format may be better suited for such
!           purposes.
            </para>

!           <para><literal>csv</literal> format
!           <indexterm>
!            <primary>CSV (Comma-Separated Values) format</primary>
!            <secondary>in psql</secondary>
!           </indexterm>
!           writes column values separated by commas, applying the quoting
!           rules described in
!           <ulink url="https://tools.ietf.org/html/rfc4180">RFC 4180</ulink>.
!           This output is compatible with the CSV format of the server's
!           <command>COPY</command> command.
!           A header line with column names is generated unless
!           the <literal>tuples_only</literal> parameter is
!           <literal>on</literal>. Titles and footers are not printed.
!           Each row is terminated by the system-dependent end-of-line character,
!           which is typically a single newline (<literal>\n</literal>) for
!           Unix-like systems or a carriage return and newline sequence
!           (<literal>\r\n</literal>) for Microsoft Windows.
!           Field separator characters other than comma can be selected with
!           <command>\pset fieldsep_csv</command>.
            </para>

            <para><literal>wrapped</literal> format is like <literal>aligned</literal> but wraps
*************** lo_import 152801
*** 2620,2627 ****
            language. They are not complete documents! This might not be
            necessary in <acronym>HTML</acronym>, but in
            <application>LaTeX</application> you must have a complete
!           document wrapper.  <literal>latex-longtable</literal>
!           also requires the <application>LaTeX</application>
            <literal>longtable</literal> and <literal>booktabs</literal> packages.
            </para>
            </listitem>
--- 2669,2680 ----
            language. They are not complete documents! This might not be
            necessary in <acronym>HTML</acronym>, but in
            <application>LaTeX</application> you must have a complete
!           document wrapper.
!           The <literal>latex</literal> format
!           uses <application>LaTeX</application>'s <literal>tabular</literal>
!           environment.
!           The <literal>latex-longtable</literal> format
!           requires the <application>LaTeX</application>
            <literal>longtable</literal> and <literal>booktabs</literal> packages.
            </para>
            </listitem>
*************** first second
*** 4588,4604 ****

  peter@localhost testdb=> <userinput>\pset border 1</userinput>
  Border style is 1.
! peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
! Output format is unaligned.
! peter@localhost testdb=> <userinput>\pset fieldsep ","</userinput>
! Field separator is ",".
  peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
! Showing only tuples.
  peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
  one,1
  two,2
  three,3
  four,4
  </programlisting>
    Alternatively, use the short commands:
  <programlisting>
--- 4641,4664 ----

  peter@localhost testdb=> <userinput>\pset border 1</userinput>
  Border style is 1.
! peter@localhost testdb=> <userinput>\pset format csv</userinput>
! Output format is csv.
  peter@localhost testdb=> <userinput>\pset tuples_only</userinput>
! Tuples only is on.
  peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
  one,1
  two,2
  three,3
  four,4
+ peter@localhost testdb=> <userinput>\pset format unaligned</userinput>
+ Output format is unaligned.
+ peter@localhost testdb=> <userinput>\pset fieldsep '\t'</userinput>
+ Field separator is "    ".
+ peter@localhost testdb=> <userinput>SELECT second, first FROM my_table;</userinput>
+ one     1
+ two     2
+ three   3
+ four    4
  </programlisting>
    Alternatively, use the short commands:
  <programlisting>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ee88e1c..a18c8a7 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*************** exec_command_pset(PsqlScanState scan_sta
*** 1957,1964 ****

              int            i;
              static const char *const my_list[] = {
!                 "border", "columns", "expanded", "fieldsep", "fieldsep_zero",
!                 "footer", "format", "linestyle", "null",
                  "numericlocale", "pager", "pager_min_lines",
                  "recordsep", "recordsep_zero",
                  "tableattr", "title", "tuples_only",
--- 1957,1964 ----

              int            i;
              static const char *const my_list[] = {
!                 "border", "columns", "expanded", "fieldsep", "fieldsep_csv",
!                 "fieldsep_zero", "footer", "format", "linestyle", "null",
                  "numericlocale", "pager", "pager_min_lines",
                  "recordsep", "recordsep_zero",
                  "tableattr", "title", "tuples_only",
*************** _align2string(enum printFormat in)
*** 3616,3621 ****
--- 3616,3624 ----
          case PRINT_ASCIIDOC:
              return "asciidoc";
              break;
+         case PRINT_CSV:
+             return "csv";
+             break;
          case PRINT_HTML:
              return "html";
              break;
*************** do_pset(const char *param, const char *v
*** 3696,3701 ****
--- 3699,3705 ----
              /* remember to update error message below when adding more */
              {"aligned", PRINT_ALIGNED},
              {"asciidoc", PRINT_ASCIIDOC},
+             {"csv", PRINT_CSV},
              {"html", PRINT_HTML},
              {"latex", PRINT_LATEX},
              {"latex-longtable", PRINT_LATEX_LONGTABLE},
*************** do_pset(const char *param, const char *v
*** 3727,3733 ****
              }
              if (match_pos < 0)
              {
!                 psql_error("\\pset: allowed formats are aligned, asciidoc, html, latex, latex-longtable, troff-ms,
unaligned,wrapped\n"); 
                  return false;
              }
              else
--- 3731,3737 ----
              }
              if (match_pos < 0)
              {
!                 psql_error("\\pset: allowed formats are aligned, asciidoc, csv, html, latex, latex-longtable,
troff-ms,unaligned, wrapped\n"); 
                  return false;
              }
              else
*************** do_pset(const char *param, const char *v
*** 3865,3870 ****
--- 3869,3895 ----
          popt->topt.fieldSep.separator_zero = true;
      }

+     /* field separator for CSV format */
+     else if (strcmp(param, "fieldsep_csv") == 0)
+     {
+         if (value)
+         {
+             /* CSV separator has to be a one-byte character */
+             if (strlen(value) != 1)
+             {
+                 psql_error("\\pset: the CSV field separator must be a single character\n");
+                 return false;
+             }
+             if (value[0] == '"' || value[0] == '\n' || value[0] == '\r')
+             {
+                 psql_error("\\pset: the CSV field separator cannot be a double quote, a newline, or a carriage
return\n");
+                 return false;
+             }
+             free(popt->topt.fieldSepCsv);
+             popt->topt.fieldSepCsv = pg_strdup(value);
+         }
+     }
+
      /* record separator for unaligned text */
      else if (strcmp(param, "recordsep") == 0)
      {
*************** printPsetInfo(const char *param, struct
*** 4013,4018 ****
--- 4038,4050 ----
          printf(_("Field separator is zero byte.\n"));
      }

+     /* show field separator for CSV format */
+     else if (strcmp(param, "fieldsep_csv") == 0)
+     {
+         printf(_("Field separator for CSV is \"%s\".\n"),
+                popt->topt.fieldSepCsv);
+     }
+
      /* show disable "(x rows)" footer */
      else if (strcmp(param, "footer") == 0)
      {
*************** pset_value_string(const char *param, str
*** 4207,4212 ****
--- 4239,4246 ----
          return pset_quoted_string(popt->topt.fieldSep.separator
                                    ? popt->topt.fieldSep.separator
                                    : "");
+     else if (strcmp(param, "fieldsep_csv") == 0)
+         return pset_quoted_string(popt->topt.fieldSepCsv);
      else if (strcmp(param, "fieldsep_zero") == 0)
          return pstrdup(pset_bool_string(popt->topt.fieldSep.separator_zero));
      else if (strcmp(param, "footer") == 0)
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 586aebd..961acd9 100644
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
*************** usage(unsigned short int pager)
*** 108,113 ****
--- 108,114 ----

      fprintf(output, _("\nOutput format options:\n"));
      fprintf(output, _("  -A, --no-align           unaligned table output mode\n"));
+     fprintf(output, _("      --csv                CSV (Comma-Separated Values) table output mode\n"));
      fprintf(output, _("  -F, --field-separator=STRING\n"
                        "                           field separator for unaligned output (default: \"%s\")\n"),
              DEFAULT_FIELD_SEP);
*************** slashUsage(unsigned short int pager)
*** 272,281 ****
      fprintf(output, _("  \\H                     toggle HTML output mode (currently %s)\n"),
              ON(pset.popt.topt.format == PRINT_HTML));
      fprintf(output, _("  \\pset [NAME [VALUE]]   set table output option\n"
!                       "                         (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|\n"
!                       "                         footer|format|linestyle|null|numericlocale|pager|\n"
!                       "                         pager_min_lines|recordsep|recordsep_zero|tableattr|title|\n"
!                       "                         tuples_only|unicode_border_linestyle|\n"
                        "                         unicode_column_linestyle|unicode_header_linestyle})\n"));
      fprintf(output, _("  \\t [on|off]            show only rows (currently %s)\n"),
              ON(pset.popt.topt.tuples_only));
--- 273,282 ----
      fprintf(output, _("  \\H                     toggle HTML output mode (currently %s)\n"),
              ON(pset.popt.topt.format == PRINT_HTML));
      fprintf(output, _("  \\pset [NAME [VALUE]]   set table output option\n"
!                       "                         (NAME := {border|columns|expanded|fieldsep|fieldsep_csv|\n"
!                       "                         fieldsep_zero|footer|format|linestyle|null|numericlocale|\n"
!                       "                         pager|pager_min_lines|recordsep|recordsep_zero|tableattr|\n"
!                       "                         title|tuples_only|unicode_border_linestyle|\n"
                        "                         unicode_column_linestyle|unicode_header_linestyle})\n"));
      fprintf(output, _("  \\t [on|off]            show only rows (currently %s)\n"),
              ON(pset.popt.topt.tuples_only));
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 69e617e..93d0b95 100644
*** a/src/bin/psql/settings.h
--- b/src/bin/psql/settings.h
***************
*** 13,18 ****
--- 13,19 ----
  #include "fe_utils/print.h"

  #define DEFAULT_FIELD_SEP "|"
+ #define DEFAULT_FIELD_SEP_CSV ","
  #define DEFAULT_RECORD_SEP "\n"

  #if defined(WIN32) || defined(__CYGWIN__)
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index be57574..84d02f4 100644
*** a/src/bin/psql/startup.c
--- b/src/bin/psql/startup.c
*************** main(int argc, char *argv[])
*** 144,149 ****
--- 144,151 ----
      pset.popt.topt.stop_table = true;
      pset.popt.topt.default_footer = true;

+     pset.popt.topt.fieldSepCsv = pg_strdup(DEFAULT_FIELD_SEP_CSV);
+
      pset.popt.topt.unicode_border_linestyle = UNICODE_LINESTYLE_SINGLE;
      pset.popt.topt.unicode_column_linestyle = UNICODE_LINESTYLE_SINGLE;
      pset.popt.topt.unicode_header_linestyle = UNICODE_LINESTYLE_SINGLE;
*************** parse_psql_options(int argc, char *argv[
*** 468,473 ****
--- 470,476 ----
          {"expanded", no_argument, NULL, 'x'},
          {"no-psqlrc", no_argument, NULL, 'X'},
          {"help", optional_argument, NULL, 1},
+         {"csv", no_argument, NULL, 2},
          {NULL, 0, NULL, 0}
      };

*************** parse_psql_options(int argc, char *argv[
*** 658,663 ****
--- 661,669 ----
                      exit(EXIT_SUCCESS);
                  }
                  break;
+             case 2:
+                 pset.popt.topt.format = PRINT_CSV;
+                 break;
              default:
          unknown_option:
                  fprintf(stderr, _("Try \"%s --help\" for more information.\n"),
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 9dbd555..b51fd57 100644
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*************** psql_completion(const char *text, int st
*** 2605,2610 ****
--- 2605,2611 ----
      /* Complete CREATE EVENT TRIGGER <name> ON with event_type */
      else if (Matches("CREATE", "EVENT", "TRIGGER", MatchAny, "ON"))
          COMPLETE_WITH("ddl_command_start", "ddl_command_end", "sql_drop");
+
      /*
       * Complete CREATE EVENT TRIGGER <name> ON <event_type>.  EXECUTE FUNCTION
       * is the recommended grammar instead of EXECUTE PROCEDURE in version 11
*************** psql_completion(const char *text, int st
*** 3525,3531 ****
          COMPLETE_WITH_QUERY(Query_for_list_of_roles);
      else if (TailMatchesCS("\\pset"))
          COMPLETE_WITH_CS("border", "columns", "expanded",
!                          "fieldsep", "fieldsep_zero", "footer", "format",
                           "linestyle", "null", "numericlocale",
                           "pager", "pager_min_lines",
                           "recordsep", "recordsep_zero",
--- 3526,3533 ----
          COMPLETE_WITH_QUERY(Query_for_list_of_roles);
      else if (TailMatchesCS("\\pset"))
          COMPLETE_WITH_CS("border", "columns", "expanded",
!                          "fieldsep", "fieldsep_csv", "fieldsep_zero",
!                          "footer", "format",
                           "linestyle", "null", "numericlocale",
                           "pager", "pager_min_lines",
                           "recordsep", "recordsep_zero",
*************** psql_completion(const char *text, int st
*** 3536,3542 ****
      else if (TailMatchesCS("\\pset", MatchAny))
      {
          if (TailMatchesCS("format"))
!             COMPLETE_WITH_CS("aligned", "asciidoc", "html", "latex",
                               "latex-longtable", "troff-ms", "unaligned",
                               "wrapped");
          else if (TailMatchesCS("linestyle"))
--- 3538,3544 ----
      else if (TailMatchesCS("\\pset", MatchAny))
      {
          if (TailMatchesCS("format"))
!             COMPLETE_WITH_CS("aligned", "asciidoc", "csv", "html", "latex",
                               "latex-longtable", "troff-ms", "unaligned",
                               "wrapped");
          else if (TailMatchesCS("linestyle"))
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index cb9a9a0..3b07280 100644
*** a/src/fe_utils/print.c
--- b/src/fe_utils/print.c
*************** print_aligned_vertical(const printTableC
*** 1737,1742 ****
--- 1737,1846 ----


  /**********************/
+ /* CSV format          */
+ /**********************/
+
+
+ static void
+ csv_escaped_print(const char *str, FILE *fout)
+ {
+     const char *p;
+
+     fputc('"', fout);
+     for (p = str; *p; p++)
+     {
+         if (*p == '"')
+             fputc('"', fout);    /* double quotes are doubled */
+         fputc(*p, fout);
+     }
+     fputc('"', fout);
+ }
+
+ static void
+ csv_print_field(const char *str, 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(str, sep) != NULL) ||
+         strcspn(str, "\r\n\"") != strlen(str))
+         csv_escaped_print(str, fout);
+     else
+         fputs(str, fout);
+ }
+
+ static void
+ print_csv_text(const printTableContent *cont, FILE *fout)
+ {
+     const char *const *ptr;
+     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.
+      *
+      * Despite RFC 4180 saying that end of lines are CRLF, terminate lines
+      * with '\n', which prints out as the system-dependent EOL string in text
+      * mode (typically LF on Unix and CRLF on Windows).
+      */
+     if (cont->opt->start_table && !cont->opt->tuples_only)
+     {
+         /* print headers */
+         for (ptr = cont->headers; *ptr; ptr++)
+         {
+             if (ptr != cont->headers)
+                 fputs(cont->opt->fieldSepCsv, fout);
+             csv_print_field(*ptr, fout, cont->opt->fieldSepCsv);
+         }
+         fputc('\n', fout);
+     }
+
+     /* print cells */
+     for (i = 0, ptr = cont->cells; *ptr; i++, ptr++)
+     {
+         csv_print_field(*ptr, fout, cont->opt->fieldSepCsv);
+         if ((i + 1) % cont->ncolumns)
+             fputs(cont->opt->fieldSepCsv, fout);
+         else
+             fputc('\n', fout);
+     }
+ }
+
+ static void
+ print_csv_vertical(const printTableContent *cont, FILE *fout)
+ {
+     const char *const *ptr;
+     int            i;
+
+     /* print records */
+     for (i = 0, ptr = cont->cells; *ptr; i++, ptr++)
+     {
+         if (cancel_pressed)
+             return;
+
+         /* print name of column */
+         csv_print_field(cont->headers[i % cont->ncolumns], fout,
+                         cont->opt->fieldSepCsv);
+
+         /* print field separator */
+         fputs(cont->opt->fieldSepCsv, fout);
+
+         /* print field value */
+         csv_print_field(*ptr, fout, cont->opt->fieldSepCsv);
+
+         fputc('\n', fout);
+     }
+ }
+
+
+ /**********************/
  /* HTML printing ******/
  /**********************/

*************** printTable(const printTableContent *cont
*** 3234,3239 ****
--- 3338,3349 ----
              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 b761349..665a782 100644
*** a/src/include/fe_utils/print.h
--- b/src/include/fe_utils/print.h
*************** enum printFormat
*** 28,33 ****
--- 28,34 ----
      PRINT_NOTHING = 0,            /* to make sure someone initializes this */
      PRINT_ALIGNED,
      PRINT_ASCIIDOC,
+     PRINT_CSV,
      PRINT_HTML,
      PRINT_LATEX,
      PRINT_LATEX_LONGTABLE,
*************** typedef struct printTableOpt
*** 112,117 ****
--- 113,119 ----
      const printTextFormat *line_style;    /* line style (NULL for default) */
      struct separator fieldSep;    /* field separator for unaligned text mode */
      struct separator recordSep; /* record separator for unaligned text mode */
+     char       *fieldSepCsv;    /* field separator for csv format */
      bool        numericLocale;    /* locale-aware numeric units separator and
                                   * decimal marker */
      char       *tableAttr;        /* attributes for HTML <table ...> */
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3818cfe..ceea28f 100644
*** a/src/test/regress/expected/psql.out
--- b/src/test/regress/expected/psql.out
*************** border                   1
*** 262,267 ****
--- 262,268 ----
  columns                  0
  expanded                 off
  fieldsep                 '|'
+ fieldsep_csv             ','
  fieldsep_zero            off
  footer                   on
  format                   aligned
*************** last error message: division by zero
*** 3243,3245 ****
--- 3244,3389 ----
  \echo 'last error code:' :LAST_ERROR_SQLSTATE
  last error code: 22012
  \unset FETCH_COUNT
+ -- test csv format
+ -- test multi-line headers, wrapping, quoting rules and newline indicators
+ prepare q as select 'ab,cd' as col1,
+   'ab' as "col,2",
+   E'a\tb' as col3,
+   '"' as col4,
+   '""' as col5,
+   'a"b' as "col""6",
+   E'a\nb' as col7,
+   NULL as col8,
+   'ab' as "col
+ 9",
+   'cd' as "col
+
+ 10",
+   array['ab', E'cd\nef'] as col11,
+   '{"a":"a,b", "a,b":null, "c":"a,\"b"}'::json as col12
+   from generate_series(1,2);
+ \pset format csv
+ \pset fieldsep_csv ','
+ \pset expanded off
+ \t off
+ execute q;
+ col1,"col,2",col3,col4,col5,"col""6",col7,col8,"col
+ 9","col
+
+ 10",col11,col12
+ "ab,cd",ab,a    b,"""","""""","a""b","a
+ b",,ab,cd,"{ab,""cd
+ ef""}","{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ "ab,cd",ab,a    b,"""","""""","a""b","a
+ b",,ab,cd,"{ab,""cd
+ ef""}","{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ \pset fieldsep_csv '\t'
+ execute q;
+ col1    col,2    col3    col4    col5    "col""6"    col7    col8    "col
+ 9"    "col
+
+ 10"    col11    col12
+ ab,cd    ab    "a    b"    """"    """"""    "a""b"    "a
+ b"        ab    cd    "{ab,""cd
+ ef""}"    "{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ ab,cd    ab    "a    b"    """"    """"""    "a""b"    "a
+ b"        ab    cd    "{ab,""cd
+ ef""}"    "{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ \t on
+ execute q;
+ ab,cd    ab    "a    b"    """"    """"""    "a""b"    "a
+ b"        ab    cd    "{ab,""cd
+ ef""}"    "{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ ab,cd    ab    "a    b"    """"    """"""    "a""b"    "a
+ b"        ab    cd    "{ab,""cd
+ ef""}"    "{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ \t off
+ \pset expanded on
+ execute q;
+ col1    ab,cd
+ col,2    ab
+ col3    "a    b"
+ col4    """"
+ col5    """"""
+ "col""6"    "a""b"
+ col7    "a
+ b"
+ col8
+ "col
+ 9"    ab
+ "col
+
+ 10"    cd
+ col11    "{ab,""cd
+ ef""}"
+ col12    "{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ col1    ab,cd
+ col,2    ab
+ col3    "a    b"
+ col4    """"
+ col5    """"""
+ "col""6"    "a""b"
+ col7    "a
+ b"
+ col8
+ "col
+ 9"    ab
+ "col
+
+ 10"    cd
+ col11    "{ab,""cd
+ ef""}"
+ col12    "{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ \pset fieldsep_csv ','
+ execute q;
+ col1,"ab,cd"
+ "col,2",ab
+ col3,a    b
+ col4,""""
+ col5,""""""
+ "col""6","a""b"
+ col7,"a
+ b"
+ col8,
+ "col
+ 9",ab
+ "col
+
+ 10",cd
+ col11,"{ab,""cd
+ ef""}"
+ col12,"{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ col1,"ab,cd"
+ "col,2",ab
+ col3,a    b
+ col4,""""
+ col5,""""""
+ "col""6","a""b"
+ col7,"a
+ b"
+ col8,
+ "col
+ 9",ab
+ "col
+
+ 10",cd
+ col11,"{ab,""cd
+ ef""}"
+ col12,"{""a"":""a,b"", ""a,b"":null, ""c"":""a,\""b""}"
+ -- illegal csv separators
+ \pset fieldsep_csv ''
+ \pset: the CSV field separator must be a single character
+ \pset fieldsep_csv ',,'
+ \pset: the CSV field separator must be a single character
+ \pset fieldsep_csv '\0'
+ \pset: the CSV field separator must be a single character
+ \pset fieldsep_csv '\n'
+ \pset: the CSV field separator cannot be a double quote, a newline, or a carriage return
+ \pset fieldsep_csv '\r'
+ \pset: the CSV field separator cannot be a double quote, a newline, or a carriage return
+ \pset fieldsep_csv '"'
+ \pset: the CSV field separator cannot be a double quote, a newline, or a carriage return
+ deallocate q;
+ \pset format aligned
+ \pset expanded off
+ \t off
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index b45da9b..fd2d0df 100644
*** a/src/test/regress/sql/psql.sql
--- b/src/test/regress/sql/psql.sql
*************** select 1/(15-unique2) from tenk1 order b
*** 688,690 ****
--- 688,737 ----
  \echo 'last error code:' :LAST_ERROR_SQLSTATE

  \unset FETCH_COUNT
+
+ -- test csv format
+ -- test multi-line headers, wrapping, quoting rules and newline indicators
+ prepare q as select 'ab,cd' as col1,
+   'ab' as "col,2",
+   E'a\tb' as col3,
+   '"' as col4,
+   '""' as col5,
+   'a"b' as "col""6",
+   E'a\nb' as col7,
+   NULL as col8,
+   'ab' as "col
+ 9",
+   'cd' as "col
+
+ 10",
+   array['ab', E'cd\nef'] as col11,
+   '{"a":"a,b", "a,b":null, "c":"a,\"b"}'::json as col12
+   from generate_series(1,2);
+
+ \pset format csv
+ \pset fieldsep_csv ','
+ \pset expanded off
+ \t off
+ execute q;
+ \pset fieldsep_csv '\t'
+ execute q;
+ \t on
+ execute q;
+ \t off
+ \pset expanded on
+ execute q;
+ \pset fieldsep_csv ','
+ execute q;
+
+ -- illegal csv separators
+ \pset fieldsep_csv ''
+ \pset fieldsep_csv ',,'
+ \pset fieldsep_csv '\0'
+ \pset fieldsep_csv '\n'
+ \pset fieldsep_csv '\r'
+ \pset fieldsep_csv '"'
+
+ deallocate q;
+ \pset format aligned
+ \pset expanded off
+ \t off

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: SSL tests failing with "ee key too small" error on Debian SID
Next
From: Tomas Vondra
Date:
Subject: Re: shared-memory based stats collector