aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/bin/psql/command.c39
-rw-r--r--src/bin/psql/help.c16
-rw-r--r--src/bin/psql/settings.h1
-rw-r--r--src/bin/psql/startup.c7
-rw-r--r--src/bin/psql/tab-complete.c5
-rw-r--r--src/fe_utils/print.c131
-rw-r--r--src/include/fe_utils/print.h2
-rw-r--r--src/test/regress/expected/psql.out89
-rw-r--r--src/test/regress/sql/psql.sql48
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
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-10 01:32:32 +0000