On Thu, Jan 26, 2006 at 10:17:05PM -0500, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> > I have seed database scripts quasi-generated from pg_dump which
> > include COPY statements, but the data is hard to edit (especially
> > cut & paste operations) when the COPY delimiter is some
> > non-visible character like \t.
>
> This seems like an awfully weak use-case for adding to pg_dump's
> already overly complicated feature set.
Those who don't use it will never see it.
> The difficulty of parsing COPY output is not simplified by making
> the delimiter variable --- more likely the reverse.
It's fairly straight-forward.
> Furthermore, it's quite unclear why you'd use pg_dump at all to
> generate a data file that you intend to feed to some other program.
In my case, it's about being copy/paste friendly.
> Seems to me that "psql -c 'COPY ...'" is a more likely front-end for
> such a process.
Actually, it's not. I'm attaching my preliminary patch, as I see I
haven't explained it well enough.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
? pg_dump_copy.diff
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.81
diff -c -r1.81 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 1 Nov 2005 21:09:50 -0000 1.81
--- doc/src/sgml/ref/pg_dump.sgml 27 Jan 2006 02:22:41 -0000
***************
*** 163,168 ****
--- 163,188 ----
</varlistentry>
<varlistentry>
+ <term><option>--copy-delimiter=<replaceable
class="parameter">delimiter</replaceable></option></term>
+ <listitem>
+ <para>
+ Use <replaceable class="parameter">delimiter</replaceable>
+ instead of the default tab character in <command>COPY</command>
statements.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>--copy-null=<replaceable
class="parameter">string_for_nulls</replaceable></option></term>
+ <listitem>
+ <para>
+ Use <replaceable class="parameter">string_for_nulls</replaceable>
instead of the
+ default \N in <command>COPY</command> statements.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-d</option></term>
<term><option>--inserts</option></term>
<listitem>
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.427
diff -c -r1.427 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 21 Jan 2006 02:16:20 -0000 1.427
--- src/bin/pg_dump/pg_dump.c 27 Jan 2006 02:22:48 -0000
***************
*** 111,116 ****
--- 111,121 ----
/* flag to turn on/off dollar quoting */
static int disable_dollar_quoting = 0;
+ /* Things used when caller invokes COPY options. */
+ const char *copy_delimiter_default = "\t";
+ const char *copy_delimiter = "\t";
+ const char *copy_null_default = "\\N";
+ const char *copy_null = "\\N";
static void help(const char *progname);
static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
***************
*** 246,251 ****
--- 251,265 ----
{"disable-triggers", no_argument, &disable_triggers, 1},
{"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
+ /*
+ * The following options don't have an equivalent short option
+ * letter, and are not available as -X long-name. Use the
+ * long form instead.
+ */
+
+ {"copy-delimiter", required_argument, NULL, 2},
+ {"copy-null", required_argument, NULL, 3},
+
{NULL, 0, NULL, 0}
};
int optindex;
***************
*** 414,419 ****
--- 428,453 ----
break;
/* This covers the long options equivalent to
-X xxx. */
+ case 2:
+ copy_delimiter = strdup(optarg);
+ if (strlen(copy_delimiter) != 1)
+ {
+ fprintf(stderr, _("In %s,
copy-delimiter must be exactly one byte long, not %d\n"),
+ progname,
strlen(copy_delimiter));
+ exit(1);
+ }
+ if ( (*copy_delimiter == '\r') ||
(*copy_delimiter == '\n') )
+ {
+ fprintf(stderr, _("In %s,
copy-delimiter may not be \\r or \\n.\n"),
+ progname);
+ exit(1);
+ }
+ break;
+
+ case 3:
+ copy_null = strdup(optarg);
+ break;
+
case 0:
break;
***************
*** 816,836 ****
column_list = fmtCopyColumnList(tbinfo);
else
column_list = ""; /* can't select columns in COPY
*/
- if (oids && hasoids)
- {
- appendPQExpBuffer(q, "COPY %s %s WITH OIDS TO stdout;",
-
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
-
classname),
- column_list);
- }
- else
- {
- appendPQExpBuffer(q, "COPY %s %s TO stdout;",
-
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
-
classname),
- column_list);
- }
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
--- 850,869 ----
column_list = fmtCopyColumnList(tbinfo);
else
column_list = ""; /* can't select columns in COPY
*/
+ appendPQExpBuffer(q, "COPY %s %s %sTO stdout",
+
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name, classname),
+ column_list,
+ /* dump OIDs if requested */
+ (oids && hasoids) ? "WITH OIDS " : ""
+ );
+ /* Add a DELIMITER if copy_delimiter is not the default */
+ if (strcmp(copy_delimiter, copy_delimiter_default) != 0)
+ appendPQExpBuffer(q, " DELIMITER AS '%s'", copy_delimiter);
+ /* Add a NULL AS stanza if copy_null is not the default */
+ if (strcmp(copy_null,copy_null_default) != 0)
+ appendPQExpBuffer(q, " NULL AS '%s'", copy_null);
+ appendPQExpBuffer(q, ";");
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
***************
*** 1085,1093 ****
/* must use 2 steps here 'cause fmtId is nonreentrant */
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
! fmtCopyColumnList(tbinfo),
! (tdinfo->oids && tbinfo->hasoids) ?
"WITH OIDS " : "");
copyStmt = copyBuf->data;
}
else
--- 1118,1135 ----
/* must use 2 steps here 'cause fmtId is nonreentrant */
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(copyBuf, "%s %sFROM stdin",
! fmtCopyColumnList(tbinfo),
! (tdinfo->oids &&
tbinfo->hasoids) ? "WITH OIDS " : ""
! );
! /* Add DELIMITER AS stanza if not the default */
! if (strcmp(copy_delimiter, copy_delimiter_default) != 0)
! appendPQExpBuffer(copyBuf, " DELIMITER AS '%s'",
copy_delimiter);
! /* Add NULL AS stanza if not the default */
! if (strcmp(copy_null, copy_null_default) != 0)
! appendPQExpBuffer(copyBuf, " NULL AS '%s'", copy_null);
! appendPQExpBuffer(copyBuf, ";\n");
!
copyStmt = copyBuf->data;
}
else
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
