diff options
Diffstat (limited to 'src')
-rw-r--r-- | src/bin/psql/command.c | 39 | ||||
-rw-r--r-- | src/bin/psql/help.c | 16 | ||||
-rw-r--r-- | src/bin/psql/settings.h | 1 | ||||
-rw-r--r-- | src/bin/psql/startup.c | 7 | ||||
-rw-r--r-- | src/bin/psql/tab-complete.c | 5 | ||||
-rw-r--r-- | src/fe_utils/print.c | 131 | ||||
-rw-r--r-- | src/include/fe_utils/print.h | 2 | ||||
-rw-r--r-- | src/test/regress/expected/psql.out | 89 | ||||
-rw-r--r-- | src/test/regress/sql/psql.sql | 48 |
9 files changed, 323 insertions, 15 deletions
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 13d4c5792f3..4a298ef0c5b 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -1957,8 +1957,8 @@ exec_command_pset(PsqlScanState scan_state, bool active_branch) int i; static const char *const my_list[] = { - "border", "columns", "expanded", "fieldsep", "fieldsep_zero", - "footer", "format", "linestyle", "null", + "border", "columns", "csv_fieldsep", "expanded", "fieldsep", + "fieldsep_zero", "footer", "format", "linestyle", "null", "numericlocale", "pager", "pager_min_lines", "recordsep", "recordsep_zero", "tableattr", "title", "tuples_only", @@ -3616,6 +3616,9 @@ _align2string(enum printFormat in) case PRINT_ASCIIDOC: return "asciidoc"; break; + case PRINT_CSV: + return "csv"; + break; case PRINT_HTML: return "html"; break; @@ -3696,6 +3699,7 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet) /* remember to update error message below when adding more */ {"aligned", PRINT_ALIGNED}, {"asciidoc", PRINT_ASCIIDOC}, + {"csv", PRINT_CSV}, {"html", PRINT_HTML}, {"latex", PRINT_LATEX}, {"troff-ms", PRINT_TROFF_MS}, @@ -3737,7 +3741,7 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet) } else { - psql_error("\\pset: allowed formats are aligned, asciidoc, html, latex, latex-longtable, troff-ms, unaligned, wrapped\n"); + psql_error("\\pset: allowed formats are aligned, asciidoc, csv, html, latex, latex-longtable, troff-ms, unaligned, wrapped\n"); return false; } } @@ -3836,6 +3840,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet) popt->topt.expanded = !popt->topt.expanded; } + /* field separator for CSV format */ + else if (strcmp(param, "csv_fieldsep") == 0) + { + if (value) + { + /* CSV separator has to be a one-byte character */ + if (strlen(value) != 1) + { + psql_error("\\pset: csv_fieldsep must be a single one-byte character\n"); + return false; + } + if (value[0] == '"' || value[0] == '\n' || value[0] == '\r') + { + psql_error("\\pset: csv_fieldsep cannot be a double quote, a newline, or a carriage return\n"); + return false; + } + popt->topt.csvFieldSep[0] = value[0]; + } + } + /* locale-aware numeric output */ else if (strcmp(param, "numericlocale") == 0) { @@ -4006,6 +4030,13 @@ printPsetInfo(const char *param, struct printQueryOpt *popt) printf(_("Expanded display is off.\n")); } + /* show field separator for CSV format */ + else if (strcmp(param, "csv_fieldsep") == 0) + { + printf(_("Field separator for CSV is \"%s\".\n"), + popt->topt.csvFieldSep); + } + /* show field separator for unaligned text */ else if (strcmp(param, "fieldsep") == 0) { @@ -4207,6 +4238,8 @@ pset_value_string(const char *param, struct printQueryOpt *popt) return psprintf("%d", popt->topt.border); else if (strcmp(param, "columns") == 0) return psprintf("%d", popt->topt.columns); + else if (strcmp(param, "csv_fieldsep") == 0) + return pset_quoted_string(popt->topt.csvFieldSep); else if (strcmp(param, "expanded") == 0) return pstrdup(popt->topt.expanded == 2 ? "auto" diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 586aebddd3d..2e9fe760eb3 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -68,7 +68,7 @@ usage(unsigned short int pager) * Keep this line count in sync with the number of lines printed below! * Use "psql --help=options | wc" to count correctly. */ - output = PageOutput(61, pager ? &(pset.popt.topt) : NULL); + output = PageOutput(62, pager ? &(pset.popt.topt) : NULL); fprintf(output, _("psql is the PostgreSQL interactive terminal.\n\n")); fprintf(output, _("Usage:\n")); @@ -108,6 +108,7 @@ usage(unsigned short int pager) 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); @@ -167,7 +168,7 @@ slashUsage(unsigned short int pager) * Use "psql --help=commands | wc" to count correctly. It's okay to count * the USE_READLINE line even in builds without that. */ - output = PageOutput(125, pager ? &(pset.popt.topt) : NULL); + output = PageOutput(126, pager ? &(pset.popt.topt) : NULL); fprintf(output, _("General\n")); fprintf(output, _(" \\copyright show PostgreSQL usage and distribution terms\n")); @@ -272,11 +273,12 @@ slashUsage(unsigned short int pager) 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")); + " (border|columns|csv_fieldsep|expanded|fieldsep|\n" + " fieldsep_zero|footer|format|linestyle|null|\n" + " numericlocale|pager|pager_min_lines|recordsep|\n" + " recordsep_zero|tableattr|title|tuples_only|\n" + " unicode_border_linestyle|unicode_column_linestyle|\n" + " unicode_header_linestyle)\n")); fprintf(output, _(" \\t [on|off] show only rows (currently %s)\n"), ON(pset.popt.topt.tuples_only)); fprintf(output, _(" \\T [STRING] set HTML <table> tag attributes, or unset if none\n")); diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h index 69e617e6b5f..176c85afd0e 100644 --- a/src/bin/psql/settings.h +++ b/src/bin/psql/settings.h @@ -12,6 +12,7 @@ #include "variables.h" #include "fe_utils/print.h" +#define DEFAULT_CSV_FIELD_SEP ',' #define DEFAULT_FIELD_SEP "|" #define DEFAULT_RECORD_SEP "\n" diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c index be57574cd32..e7536a8a06f 100644 --- a/src/bin/psql/startup.c +++ b/src/bin/psql/startup.c @@ -144,6 +144,9 @@ main(int argc, char *argv[]) pset.popt.topt.stop_table = true; pset.popt.topt.default_footer = true; + pset.popt.topt.csvFieldSep[0] = DEFAULT_CSV_FIELD_SEP; + pset.popt.topt.csvFieldSep[1] = '\0'; + 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; @@ -468,6 +471,7 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts *options) {"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} }; @@ -658,6 +662,9 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts *options) 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 9dbd5551668..7993c05283d 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -2605,6 +2605,7 @@ psql_completion(const char *text, int start, int end) /* 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 @@ -3524,7 +3525,7 @@ psql_completion(const char *text, int start, int end) else if (TailMatchesCS("\\password")) COMPLETE_WITH_QUERY(Query_for_list_of_roles); else if (TailMatchesCS("\\pset")) - COMPLETE_WITH_CS("border", "columns", "expanded", + COMPLETE_WITH_CS("border", "columns", "csv_fieldsep", "expanded", "fieldsep", "fieldsep_zero", "footer", "format", "linestyle", "null", "numericlocale", "pager", "pager_min_lines", @@ -3536,7 +3537,7 @@ psql_completion(const char *text, int start, int end) else if (TailMatchesCS("\\pset", MatchAny)) { if (TailMatchesCS("format")) - COMPLETE_WITH_CS("aligned", "asciidoc", "html", "latex", + 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 cb9a9a06131..6b78f0909cd 100644 --- a/src/fe_utils/print.c +++ b/src/fe_utils/print.c @@ -1737,7 +1737,119 @@ print_aligned_vertical(const printTableContent *cont, /**********************/ -/* HTML printing ******/ +/* 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, 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. + * - the field is exactly "\.". + * - the field separator is either "\" or ".". + * The last two cases prevent producing a line that the server's COPY + * command would interpret as an end-of-data marker. We only really + * need to ensure that the complete line isn't exactly "\.", but for + * simplicity we apply stronger restrictions here. + *---------------- + */ + if (strchr(str, sep) != NULL || + strcspn(str, "\r\n\"") != strlen(str) || + strcmp(str, "\\.") == 0 || + sep == '\\' || sep == '.') + 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) + fputc(cont->opt->csvFieldSep[0], fout); + csv_print_field(*ptr, fout, cont->opt->csvFieldSep[0]); + } + fputc('\n', fout); + } + + /* print cells */ + for (i = 0, ptr = cont->cells; *ptr; i++, ptr++) + { + csv_print_field(*ptr, fout, cont->opt->csvFieldSep[0]); + if ((i + 1) % cont->ncolumns) + fputc(cont->opt->csvFieldSep[0], 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->csvFieldSep[0]); + + /* print field separator */ + fputc(cont->opt->csvFieldSep[0], fout); + + /* print field value */ + csv_print_field(*ptr, fout, cont->opt->csvFieldSep[0]); + + fputc('\n', fout); + } +} + + +/**********************/ +/* HTML */ /**********************/ @@ -1953,9 +2065,10 @@ print_html_vertical(const printTableContent *cont, FILE *fout) /*************************/ -/* ASCIIDOC */ +/* ASCIIDOC */ /*************************/ + static void asciidoc_escaped_print(const char *in, FILE *fout) { @@ -2174,6 +2287,7 @@ print_asciidoc_vertical(const printTableContent *cont, FILE *fout) } } + /*************************/ /* LaTeX */ /*************************/ @@ -2319,6 +2433,11 @@ print_latex_text(const printTableContent *cont, FILE *fout) } +/*************************/ +/* LaTeX longtable */ +/*************************/ + + static void print_latex_longtable_text(const printTableContent *cont, FILE *fout) { @@ -2564,7 +2683,7 @@ print_latex_vertical(const printTableContent *cont, FILE *fout) /*************************/ -/* Troff -ms */ +/* Troff -ms */ /*************************/ @@ -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 b761349bc72..4f7987e5cbb 100644 --- a/src/include/fe_utils/print.h +++ b/src/include/fe_utils/print.h @@ -28,6 +28,7 @@ enum printFormat PRINT_NOTHING = 0, /* to make sure someone initializes this */ PRINT_ALIGNED, PRINT_ASCIIDOC, + PRINT_CSV, PRINT_HTML, PRINT_LATEX, PRINT_LATEX_LONGTABLE, @@ -112,6 +113,7 @@ typedef struct printTableOpt 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 csvFieldSep[2]; /* 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 7f20ae2c609..b6face58e39 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -260,6 +260,7 @@ select '2000-01-01'::date as party_over \pset border 1 columns 0 +csv_fieldsep ',' expanded off fieldsep '|' fieldsep_zero off @@ -2937,6 +2938,94 @@ execute q; <l|int >l|2 |==== deallocate q; +-- test csv output format +\pset format csv +\pset border 1 +\pset expanded off +\d psql_serial_tab_id_seq +Type,Start,Minimum,Maximum,Increment,Cycles?,Cache +integer,1,1,2147483647,1,no,1 +\pset tuples_only true +\df exp +pg_catalog,exp,double precision,double precision,func +pg_catalog,exp,numeric,numeric,func +\pset tuples_only false +\pset expanded on +\d psql_serial_tab_id_seq +Type,integer +Start,1 +Minimum,1 +Maximum,2147483647 +Increment,1 +Cycles?,no +Cache,1 +\pset tuples_only true +\df exp +Schema,pg_catalog +Name,exp +Result data type,double precision +Argument data types,double precision +Type,func +Schema,pg_catalog +Name,exp +Result data type,numeric +Argument data types,numeric +Type,func +\pset tuples_only false +prepare q as + select 'some"text' as "a""title", E' <foo>\n<bar>' as "junk", + ' ' as "empty", n as int + from generate_series(1,2) as n; +\pset expanded off +execute q; +"a""title",junk,empty,int +"some""text"," <foo> +<bar>", ,1 +"some""text"," <foo> +<bar>", ,2 +\pset expanded on +execute q; +"a""title","some""text" +junk," <foo> +<bar>" +empty, +int,1 +"a""title","some""text" +junk," <foo> +<bar>" +empty, +int,2 +deallocate q; +-- special cases +\pset expanded off +select 'comma,comma' as comma, 'semi;semi' as semi; +comma,semi +"comma,comma",semi;semi +\pset csv_fieldsep ';' +select 'comma,comma' as comma, 'semi;semi' as semi; +comma;semi +comma,comma;"semi;semi" +select '\.' as data; +data +"\." +\pset csv_fieldsep '.' +select '\' as d1, '' as d2; +"d1"."d2" +"\"."" +-- illegal csv separators +\pset csv_fieldsep '' +\pset: csv_fieldsep must be a single one-byte character +\pset csv_fieldsep '\0' +\pset: csv_fieldsep must be a single one-byte character +\pset csv_fieldsep '\n' +\pset: csv_fieldsep cannot be a double quote, a newline, or a carriage return +\pset csv_fieldsep '\r' +\pset: csv_fieldsep cannot be a double quote, a newline, or a carriage return +\pset csv_fieldsep '"' +\pset: csv_fieldsep cannot be a double quote, a newline, or a carriage return +\pset csv_fieldsep ',,' +\pset: csv_fieldsep must be a single one-byte character +\pset csv_fieldsep ',' -- test html output format \pset format html \pset border 1 diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 82db94756ec..bfe02d12be0 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -501,6 +501,54 @@ execute q; deallocate q; +-- test csv output format + +\pset format csv + +\pset border 1 +\pset expanded off +\d psql_serial_tab_id_seq +\pset tuples_only true +\df exp +\pset tuples_only false +\pset expanded on +\d psql_serial_tab_id_seq +\pset tuples_only true +\df exp +\pset tuples_only false + +prepare q as + select 'some"text' as "a""title", E' <foo>\n<bar>' as "junk", + ' ' as "empty", n as int + from generate_series(1,2) as n; + +\pset expanded off +execute q; + +\pset expanded on +execute q; + +deallocate q; + +-- special cases +\pset expanded off +select 'comma,comma' as comma, 'semi;semi' as semi; +\pset csv_fieldsep ';' +select 'comma,comma' as comma, 'semi;semi' as semi; +select '\.' as data; +\pset csv_fieldsep '.' +select '\' as d1, '' as d2; + +-- illegal csv separators +\pset csv_fieldsep '' +\pset csv_fieldsep '\0' +\pset csv_fieldsep '\n' +\pset csv_fieldsep '\r' +\pset csv_fieldsep '"' +\pset csv_fieldsep ',,' + +\pset csv_fieldsep ',' + -- test html output format \pset format html |