Folks,
>From the earlier discussion, it appears that there is a variety of
opinions on what the COPY delimiter should be in pg_dump. This patch
allows people to set it and the NULL string. Thanks to Gavin Sherry
for help with the pointers :)
I didn't patch pg_dumpall, but it would be trivial if there's a use
case.
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
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 6 Mar 2006 07:32:05 -0000
***************
*** 163,168 ****
--- 163,208 ----
</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>--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.433
diff -c -r1.433 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 5 Mar 2006 15:58:50 -0000 1.433
--- src/bin/pg_dump/pg_dump.c 6 Mar 2006 07:32:12 -0000
***************
*** 114,119 ****
--- 114,125 ----
/* flag to turn on/off dollar quoting */
static int disable_dollar_quoting = 0;
+ /* Things used when caller invokes COPY options. */
+ #define ARG_COPY_DELIMITER 2
+ #define ARG_COPY_NULL 3
+ char *copy_delimiter = "\t";
+ char *copy_null;
+
static void help(const char *progname);
static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
***************
*** 181,186 ****
--- 187,193 ----
ExecStatusType expected);
+
int
main(int argc, char **argv)
{
***************
*** 211,217 ****
char *outputSuperuser = NULL;
RestoreOptions *ropt;
!
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"blobs", no_argument, NULL, 'b'},
--- 218,224 ----
char *outputSuperuser = NULL;
RestoreOptions *ropt;
!
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
{"blobs", no_argument, NULL, 'b'},
***************
*** 249,254 ****
--- 256,269 ----
{"disable-dollar-quoting", no_argument,
&disable_dollar_quoting, 1},
{"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. Just use
+ * the long form.
+ */
+ {"copy-delimiter", required_argument, NULL, ARG_COPY_DELIMITER},
+ {"copy-null", required_argument, NULL, ARG_COPY_NULL},
{NULL, 0, NULL, 0}
};
***************
*** 418,423 ****
--- 433,460 ----
break;
/* This covers the long options equivalent to
-X xxx. */
+ case ARG_COPY_DELIMITER:
+ if ( strlen(optarg) != 1)
+ {
+ fprintf(stderr, _("In %s,
copy-delimiter must be exactly one byte long, not %d.\n"),
+ progname,
strlen(optarg));
+ exit(1);
+ }
+ if (*optarg == '\r' || *optarg == '\n' ||
+ *optarg == '\\')
+ {
+ fprintf(stderr, _("In %s,
copy-delimiter may not be any of \\r, \\n or \\.\n"),
+ progname);
+ exit(1);
+ }
+ copy_delimiter = optarg;
+ break;
+
+ case ARG_COPY_NULL:
+ copy_null = malloc(2*strlen(optarg)+1);
+ PQescapeString(copy_null, optarg,
2*strlen(optarg)+1);
+ break;
+
case 0:
break;
***************
*** 427,432 ****
--- 464,479 ----
}
}
+ if (copy_null == NULL)
+ copy_null = malloc(3);
+ strcpy(copy_null, "\\N");
+
+ if (strstr(copy_null, copy_delimiter))
+ {
+ fprintf(stderr, _("In %s, the NULL AS string cannot contain the
COPY delimiter.\n"), progname);
+ exit(1);
+ }
+
if (optind < (argc - 1))
{
fprintf(stderr, _("%s: too many command-line arguments (first
is \"%s\")\n"),
***************
*** 702,707 ****
--- 749,756 ----
" use SESSION AUTHORIZATION
commands instead of\n"
" OWNER TO commands\n"));
+ printf(_(" --copy-delimiter string to use as column DELIMITER
in COPY statements\n"));
+ printf(_(" --copy-null string to use for NULLs in COPY
statements\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket
directory\n"));
printf(_(" -p, --port=PORT database server port number\n"));
***************
*** 844,849 ****
--- 893,904 ----
int ret;
char *copybuf;
const char *column_list;
+ char *local_copy_delimiter;
+ char *local_copy_null;
+ local_copy_delimiter = malloc(2*strlen(copy_delimiter)+1);
+ PQescapeString (local_copy_delimiter, copy_delimiter,
2*strlen(copy_delimiter)+1);
+ local_copy_null = malloc(2*strlen(copy_null)+1);
+ PQescapeString (local_copy_null, copy_null, 2*strlen(copy_null)+1);
if (g_verbose)
write_msg(NULL, "dumping contents of table %s\n", classname);
***************
*** 867,886 ****
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);
PQclear(res);
--- 922,938 ----
else
column_list = ""; /* can't select columns in COPY
*/
! /*
! * Explicitly set the DELIMITER and NULL strings in the COPY
! * statement.
! */
! appendPQExpBuffer(q, "COPY %s %s TO stdout %sDELIMITER AS '%s' NULL AS
'%s';",
!
fmtQualifiedId(tbinfo->dobj.namespace->dobj.name,
!
classname),
! column_list,
! (oids && hasoids) ? "WITH OIDS " : "",
! local_copy_delimiter,
! local_copy_null);
res = PQexec(g_conn, q->data);
check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
PQclear(res);
***************
*** 1117,1122 ****
--- 1169,1180 ----
PQExpBuffer copyBuf = createPQExpBuffer();
DataDumperPtr dumpFn;
char *copyStmt;
+ char *local_copy_delimiter;
+ char *local_copy_null;
+ local_copy_delimiter = malloc(2*strlen(copy_delimiter)+1);
+ PQescapeString (local_copy_delimiter, copy_delimiter,
2*strlen(copy_delimiter)+1);
+ local_copy_null = malloc(2*strlen(copy_null)+1);
+ PQescapeString (local_copy_null, copy_null, 2*strlen(copy_null)+1);
if (!dumpInserts)
{
***************
*** 1125,1133 ****
/* 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
--- 1183,1193 ----
/* must use 2 steps here 'cause fmtId is nonreentrant */
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(copyBuf, "%s FROM stdin %sDELIMITER AS '%s'
NULL AS '%s';\n",
! fmtCopyColumnList(tbinfo),
! (tdinfo->oids &&
tbinfo->hasoids) ? "WITH OIDS " : "",
! local_copy_delimiter,
! local_copy_null);
copyStmt = copyBuf->data;
}
else
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
