Inspired by this question http://stackoverflow.com/questions/6857265 I
have implemented a way to set the psql record and field separators to a
zero byte (ASCII NUL character). This can be very useful in shell
scripts to have an unambiguous separator. Other GNU tools such as find,
grep, sort, xargs also support this. So with this you could for example
do
psql --record-separator-zero -At -c 'select something from somewhere' | xargs
-0 dosomething
I have thought about two different ways to implement this. Attempt one
was to make the backslash command option parsing zero-byte proof top to
bottom by using PQExpBuffers, so you could then write \R '\000'. But
that turned out to be very invasive and complicated. And worst, you
couldn't use it from the command line, because psql -R '\000' doesn't
work (the octal escape syntax is not used on the command line).
So attempt two, which I present here, is to just have separate syntax to
set the separators to zero bytes. From the command line it would be
--record-separator-zero and --field-separator-zero, and from within psql
it would be \pset recordsep_zero and \pset fieldsep_zero. I don't care
much for the verbosity of this, so I'm still thinking about ways to
abbreviate this. I think the most common use of this would be to set
the record separator from the command line, so we could use a short
option such as -0 or -z for that.
Patch attached. Comments welcome.
diff --git i/doc/src/sgml/ref/psql-ref.sgml w/doc/src/sgml/ref/psql-ref.sgml
index a9b1ed2..752d6de 100644
--- i/doc/src/sgml/ref/psql-ref.sgml
+++ w/doc/src/sgml/ref/psql-ref.sgml
@@ -193,6 +193,15 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>--field-separator-zero</option></term>
+ <listitem>
+ <para>
+ Set the field separator for unaligned output to a zero byte.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-h <replaceable class="parameter">hostname</replaceable></></term>
<term><option>--host=<replaceable class="parameter">hostname</replaceable></></term>
<listitem>
@@ -320,6 +329,16 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>--record-separator-zero</option></term>
+ <listitem>
+ <para>
+ Set the record separator for unaligned output to a zero byte. This is
+ useful for interfacing, for example, with <literal>xargs -0</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-s</></term>
<term><option>--single-step</></term>
<listitem>
@@ -1909,6 +1928,16 @@ lo_import 152801
</varlistentry>
<varlistentry>
+ <term><literal>fieldsep_zero</literal></term>
+ <listitem>
+ <para>
+ Sets the field separator to use in unaligned output format to a zero
+ byte.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>footer</literal></term>
<listitem>
<para>
@@ -2078,6 +2107,16 @@ lo_import 152801
</varlistentry>
<varlistentry>
+ <term><literal>recordsep_zero</literal></term>
+ <listitem>
+ <para>
+ Sets the record separator to use in unaligned output format to a zero
+ byte.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>tableattr</literal> (or <literal>T</literal>)</term>
<listitem>
<para>
diff --git i/src/bin/psql/command.c w/src/bin/psql/command.c
index 69fac83..9421a73 100644
--- i/src/bin/psql/command.c
+++ w/src/bin/psql/command.c
@@ -2284,11 +2284,26 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
{
if (value)
{
- free(popt->topt.fieldSep);
- popt->topt.fieldSep = pg_strdup(value);
+ free(popt->topt.fieldSep.separator);
+ popt->topt.fieldSep.separator = pg_strdup(value);
+ popt->topt.fieldSep.separator_zero = false;
}
if (!quiet)
- printf(_("Field separator is \"%s\".\n"), popt->topt.fieldSep);
+ {
+ if (popt->topt.fieldSep.separator_zero)
+ printf(_("Field separator is zero byte.\n"));
+ else
+ printf(_("Field separator is \"%s\".\n"), popt->topt.fieldSep.separator);
+ }
+ }
+
+ else if (strcmp(param, "fieldsep_zero") == 0)
+ {
+ free(popt->topt.fieldSep.separator);
+ popt->topt.fieldSep.separator = NULL;
+ popt->topt.fieldSep.separator_zero = true;
+ if (!quiet)
+ printf(_("Field separator is zero byte.\n"));
}
/* record separator for unaligned text */
@@ -2296,18 +2311,30 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
{
if (value)
{
- free(popt->topt.recordSep);
- popt->topt.recordSep = pg_strdup(value);
+ free(popt->topt.recordSep.separator);
+ popt->topt.recordSep.separator = pg_strdup(value);
+ popt->topt.recordSep.separator_zero = false;
}
if (!quiet)
{
- if (strcmp(popt->topt.recordSep, "\n") == 0)
+ if (popt->topt.recordSep.separator_zero)
+ printf(_("Record separator is zero byte.\n"));
+ else if (strcmp(popt->topt.recordSep.separator, "\n") == 0)
printf(_("Record separator is <newline>."));
else
- printf(_("Record separator is \"%s\".\n"), popt->topt.recordSep);
+ printf(_("Record separator is \"%s\".\n"), popt->topt.recordSep.separator);
}
}
+ else if (strcmp(param, "recordsep_zero") == 0)
+ {
+ free(popt->topt.recordSep.separator);
+ popt->topt.recordSep.separator = NULL;
+ popt->topt.recordSep.separator_zero = true;
+ if (!quiet)
+ printf(_("Record separator is zero byte.\n"));
+ }
+
/* toggle between full and tuples-only format */
else if (strcmp(param, "t") == 0 || strcmp(param, "tuples_only") == 0)
{
diff --git i/src/bin/psql/help.c w/src/bin/psql/help.c
index 172fd0c..fbf2f76 100644
--- i/src/bin/psql/help.c
+++ w/src/bin/psql/help.c
@@ -116,10 +116,14 @@ usage(void)
printf(_(" -F, --field-separator=STRING\n"
" set field separator (default: \"%s\")\n"),
DEFAULT_FIELD_SEP);
+ printf(_(" --field-separator-zero\n"
+ " set field separator to zero byte\n"));
printf(_(" -H, --html HTML table output mode\n"));
printf(_(" -P, --pset=VAR[=ARG] set printing option VAR to ARG (see \\pset command)\n"));
printf(_(" -R, --record-separator=STRING\n"
" set record separator (default: newline)\n"));
+ printf(_(" --record-separator-zero\n"
+ " set record separator to zero byte\n"));
printf(_(" -t, --tuples-only print rows only\n"));
printf(_(" -T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)\n"));
printf(_(" -x, --expanded turn on expanded table output\n"));
@@ -237,8 +241,8 @@ 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 := {format|border|expanded|fieldsep|footer|null|\n"
- " numericlocale|recordsep|tuples_only|title|tableattr|pager})\n"));
+ " (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|\n"
+ " numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})\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 i/src/bin/psql/print.c w/src/bin/psql/print.c
index e127edb..43ddab1 100644
--- i/src/bin/psql/print.c
+++ w/src/bin/psql/print.c
@@ -268,6 +268,16 @@ fputnbytes(FILE *f, const char *str, size_t n)
}
+static void
+print_separator(struct separator sep, FILE *fout)
+{
+ if (sep.separator_zero)
+ fputc('\000', fout);
+ else if (sep.separator)
+ fputs(sep.separator, fout);
+}
+
+
/*************************/
/* Unaligned text */
/*************************/
@@ -276,8 +286,6 @@ fputnbytes(FILE *f, const char *str, size_t n)
static void
print_unaligned_text(const printTableContent *cont, FILE *fout)
{
- const char *opt_fieldsep = cont->opt->fieldSep;
- const char *opt_recordsep = cont->opt->recordSep;
bool opt_tuples_only = cont->opt->tuples_only;
unsigned int i;
const char *const * ptr;
@@ -286,16 +294,14 @@ print_unaligned_text(const printTableContent *cont, FILE *fout)
if (cancel_pressed)
return;
- if (!opt_fieldsep)
- opt_fieldsep = "";
- if (!opt_recordsep)
- opt_recordsep = "";
-
if (cont->opt->start_table)
{
/* print title */
if (!opt_tuples_only && cont->title)
- fprintf(fout, "%s%s", cont->title, opt_recordsep);
+ {
+ fputs(cont->title, fout);
+ print_separator(cont->opt->recordSep, fout);
+ }
/* print headers */
if (!opt_tuples_only)
@@ -303,7 +309,7 @@ print_unaligned_text(const printTableContent *cont, FILE *fout)
for (ptr = cont->headers; *ptr; ptr++)
{
if (ptr != cont->headers)
- fputs(opt_fieldsep, fout);
+ print_separator(cont->opt->fieldSep, fout);
fputs(*ptr, fout);
}
need_recordsep = true;
@@ -318,7 +324,7 @@ print_unaligned_text(const printTableContent *cont, FILE *fout)
{
if (need_recordsep)
{
- fputs(opt_recordsep, fout);
+ print_separator(cont->opt->recordSep, fout);
need_recordsep = false;
if (cancel_pressed)
break;
@@ -326,7 +332,7 @@ print_unaligned_text(const printTableContent *cont, FILE *fout)
fputs(*ptr, fout);
if ((i + 1) % cont->ncolumns)
- fputs(opt_fieldsep, fout);
+ print_separator(cont->opt->fieldSep, fout);
else
need_recordsep = true;
}
@@ -342,7 +348,7 @@ print_unaligned_text(const printTableContent *cont, FILE *fout)
{
if (need_recordsep)
{
- fputs(opt_recordsep, fout);
+ print_separator(cont->opt->recordSep, fout);
need_recordsep = false;
}
fputs(f->data, fout);
@@ -359,8 +365,6 @@ print_unaligned_text(const printTableContent *cont, FILE *fout)
static void
print_unaligned_vertical(const printTableContent *cont, FILE *fout)
{
- const char *opt_fieldsep = cont->opt->fieldSep;
- const char *opt_recordsep = cont->opt->recordSep;
bool opt_tuples_only = cont->opt->tuples_only;
unsigned int i;
const char *const * ptr;
@@ -369,11 +373,6 @@ print_unaligned_vertical(const printTableContent *cont, FILE *fout)
if (cancel_pressed)
return;
- if (!opt_fieldsep)
- opt_fieldsep = "";
- if (!opt_recordsep)
- opt_recordsep = "";
-
if (cont->opt->start_table)
{
/* print title */
@@ -393,19 +392,19 @@ print_unaligned_vertical(const printTableContent *cont, FILE *fout)
if (need_recordsep)
{
/* record separator is 2 occurrences of recordsep in this mode */
- fputs(opt_recordsep, fout);
- fputs(opt_recordsep, fout);
+ print_separator(cont->opt->recordSep, fout);
+ print_separator(cont->opt->recordSep, fout);
need_recordsep = false;
if (cancel_pressed)
break;
}
fputs(cont->headers[i % cont->ncolumns], fout);
- fputs(opt_fieldsep, fout);
+ print_separator(cont->opt->fieldSep, fout);
fputs(*ptr, fout);
if ((i + 1) % cont->ncolumns)
- fputs(opt_recordsep, fout);
+ print_separator(cont->opt->recordSep, fout);
else
need_recordsep = true;
}
@@ -417,10 +416,10 @@ print_unaligned_vertical(const printTableContent *cont, FILE *fout)
{
printTableFooter *f;
- fputs(opt_recordsep, fout);
+ print_separator(cont->opt->recordSep, fout);
for (f = cont->footers; f; f = f->next)
{
- fputs(opt_recordsep, fout);
+ print_separator(cont->opt->recordSep, fout);
fputs(f->data, fout);
}
}
diff --git i/src/bin/psql/print.h w/src/bin/psql/print.h
index 86c6e75..931535e 100644
--- i/src/bin/psql/print.h
+++ w/src/bin/psql/print.h
@@ -67,6 +67,12 @@ typedef struct printTextFormat
* marks when border=0? */
} printTextFormat;
+struct separator
+{
+ char *separator;
+ bool separator_zero;
+};
+
typedef struct printTableOpt
{
enum printFormat format; /* see enum above */
@@ -81,8 +87,8 @@ typedef struct printTableOpt
bool stop_table; /* print stop decoration, eg </table> */
unsigned long prior_records; /* start offset for record counters */
const printTextFormat *line_style; /* line style (NULL for default) */
- char *fieldSep; /* field separator for unaligned text mode */
- char *recordSep; /* record separator for unaligned text mode */
+ struct separator fieldSep; /* field separator for unaligned text mode */
+ struct separator recordSep; /* record separator for unaligned text mode */
bool numericLocale; /* locale-aware numeric units separator and
* decimal marker */
char *tableAttr; /* attributes for HTML <table ...> */
diff --git i/src/bin/psql/startup.c w/src/bin/psql/startup.c
index 8b1864c..69207c1 100644
--- i/src/bin/psql/startup.c
+++ w/src/bin/psql/startup.c
@@ -150,10 +150,16 @@ main(int argc, char *argv[])
parse_psql_options(argc, argv, &options);
- if (!pset.popt.topt.fieldSep)
- pset.popt.topt.fieldSep = pg_strdup(DEFAULT_FIELD_SEP);
- if (!pset.popt.topt.recordSep)
- pset.popt.topt.recordSep = pg_strdup(DEFAULT_RECORD_SEP);
+ if (!pset.popt.topt.fieldSep.separator)
+ {
+ pset.popt.topt.fieldSep.separator = pg_strdup(DEFAULT_FIELD_SEP);
+ pset.popt.topt.fieldSep.separator_zero = false;
+ }
+ if (!pset.popt.topt.recordSep.separator)
+ {
+ pset.popt.topt.recordSep.separator = pg_strdup(DEFAULT_RECORD_SEP);
+ pset.popt.topt.recordSep.separator_zero = false;
+ }
if (options.username == NULL)
password_prompt = pg_strdup(_("Password: "));
@@ -338,6 +344,7 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts * options)
{"echo-hidden", no_argument, NULL, 'E'},
{"file", required_argument, NULL, 'f'},
{"field-separator", required_argument, NULL, 'F'},
+ {"field-separator-zero", no_argument, NULL, 10},
{"host", required_argument, NULL, 'h'},
{"html", no_argument, NULL, 'H'},
{"list", no_argument, NULL, 'l'},
@@ -349,6 +356,7 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts * options)
{"pset", required_argument, NULL, 'P'},
{"quiet", no_argument, NULL, 'q'},
{"record-separator", required_argument, NULL, 'R'},
+ {"record-separator-zero", no_argument, NULL, 11},
{"single-step", no_argument, NULL, 's'},
{"single-line", no_argument, NULL, 'S'},
{"tuples-only", no_argument, NULL, 't'},
@@ -407,7 +415,11 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts * options)
options->action_string = optarg;
break;
case 'F':
- pset.popt.topt.fieldSep = pg_strdup(optarg);
+ pset.popt.topt.fieldSep.separator = pg_strdup(optarg);
+ pset.popt.topt.fieldSep.separator_zero = false;
+ break;
+ case 10:
+ pset.popt.topt.fieldSep.separator_zero = true;
break;
case 'h':
options->host = optarg;
@@ -459,7 +471,11 @@ parse_psql_options(int argc, char *argv[], struct adhoc_opts * options)
SetVariableBool(pset.vars, "QUIET");
break;
case 'R':
- pset.popt.topt.recordSep = pg_strdup(optarg);
+ pset.popt.topt.recordSep.separator = pg_strdup(optarg);
+ pset.popt.topt.recordSep.separator_zero = false;
+ break;
+ case 11:
+ pset.popt.topt.recordSep.separator_zero = true;
break;
case 's':
SetVariableBool(pset.vars, "SINGLESTEP");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
