Re: CSV mode option for pg_dump

Bruce Momjian wrote:

>pg_dump CSV TODO item removed until we come up with something everyone
>can agree on.
>
>
>

That's a pity.

Just to show you how little is involved in what I was suggesting, a
prototype patch is attached - it's 182 lines of context diff, which is
pretty small for a new feature. It took me about an hour to write and I
have tested it against the regression db in both text and binary dump
modes, where it works without a hitch.

cheers

andrew


Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.438
diff -c -r1.438 pg_dump.c
*** src/bin/pg_dump/pg_dump.c    9 Jun 2006 19:46:09 -0000    1.438
--- src/bin/pg_dump/pg_dump.c    14 Jun 2006 00:32:03 -0000
***************
*** 113,118 ****
--- 113,123 ----
  /* flag to turn on/off dollar quoting */
  static int    disable_dollar_quoting = 0;

+ /* flag to control if using CSv */
+ static bool use_csv = false;
+
+ /* holder for CSV options */
+ static PQExpBuffer csv_opts;

  static void help(const char *progname);
  static NamespaceInfo *findNamespace(Oid nsoid, Oid objoid);
***************
*** 251,256 ****
--- 256,265 ----
          {"disable-triggers", no_argument, &disable_triggers, 1},
          {"use-set-session-authorization", no_argument, &use_setsessauth, 1},

+         /* long options with no short version */
+         {"csv", no_argument, NULL ,2},
+         {"csv-option",required_argument, NULL, 3},
+
          {NULL, 0, NULL, 0}
      };
      int            optindex;
***************
*** 285,290 ****
--- 294,301 ----
          }
      }

+     csv_opts = createPQExpBuffer();
+
      while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:oOp:RsS:t:uU:vWxX:Z:",
                              long_options, &optindex)) != -1)
      {
***************
*** 419,424 ****
--- 430,462 ----
                  break;
                  /* This covers the long options equivalent to -X xxx. */

+             case 2:  /* csv */
+                 use_csv = true;
+                 break;
+
+             case 3: /* csv-option */
+                 if (strcmp(optarg, "singlequote") == 0)
+                 {
+                     appendPQExpBuffer(csv_opts,"QUOTE AS '''' ");
+                 }
+                 else if (strcmp(optarg, "tabdelimiter") == 0)
+                 {
+                     appendPQExpBuffer(csv_opts,"DELIMITER AS E'\\t' ");
+                 }
+                 else if (strcmp(optarg, "header") == 0)
+                 {
+                     appendPQExpBuffer(csv_opts,"HEADER ");
+                 }
+                 else
+                 {
+                     fprintf(stderr,
+                             _("%s: invalid csv option -- %s\n"),
+                             progname, optarg);
+                     fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+                     exit(1);
+                 }
+
+
              case 0:
                  break;

***************
*** 463,468 ****
--- 501,518 ----
          exit(1);
      }

+     if ( use_csv == true && dumpInserts == true)
+     {
+         write_msg(NULL, "INSERT (-d, -D) and CSV (--csv) options cannot be used together\n");
+         exit(1);
+     }
+
+     if ( use_csv == false && strlen(csv_opts->data) > 0)
+     {
+         write_msg(NULL, "You must specify --csv to use --csv-option\n");
+         exit(1);
+     }
+
      /* open the output file */
      switch (format[0])
      {
***************
*** 714,719 ****
--- 764,771 ----
               "                           use SESSION AUTHORIZATION commands instead of\n"
               "                           OWNER TO commands\n"));

+     printf(_("  --csv                    use CSV mode\n"));
+     printf(_("  --csv-option=opt         one of header, tabdelimiter or singlequote\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"));
***************
*** 881,898 ****

      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);
--- 933,955 ----

      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);
      }
+     if (use_csv)
+     {
+         appendPQExpBuffer(q, " CSV %s", csv_opts->data);
+     }
+     appendPQExpBuffer(q, ";");
      res = PQexec(g_conn, q->data);
      check_sql_result(res, g_conn, q->data, PGRES_COPY_OUT);
      PQclear(res);
***************
*** 1139,1147 ****
          /* 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
--- 1196,1208 ----
          /* must use 2 steps here 'cause fmtId is nonreentrant */
          appendPQExpBuffer(copyBuf, "COPY %s ",
                            fmtId(tbinfo->dobj.name));
!         appendPQExpBuffer(copyBuf, "%s %s",
                            fmtCopyColumnList(tbinfo),
                        (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
+         appendPQExpBuffer(copyBuf, "FROM stdin");
+         if (use_csv)
+             appendPQExpBuffer(copyBuf, " CSV %s", csv_opts->data);
+         appendPQExpBuffer(copyBuf, ";\n");
          copyStmt = copyBuf->data;
      }
      else