Fabien COELHO <coe...@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