COPY CSV header line feature - Mailing list pgsql-patches

The attached patch implements the previously discussed header line
feature for CSV mode COPY. It is triggered by the keyword HEADER (blame
Bruce - he chose it ;-) ).

On input this feature causes the first line to be ignored; on output it
generates a line of column names. This will make playing with
spreadsheets potentially a lot nicer.

Docs and regression tests will be forthcoming.

cheers

andrew
Index: src/backend/commands/copy.c
===================================================================
RCS file: /home/cvsmirror/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.237
diff -c -r1.237 copy.c
*** src/backend/commands/copy.c    12 Mar 2005 05:41:34 -0000    1.237
--- src/backend/commands/copy.c    13 Mar 2005 23:25:50 -0000
***************
*** 131,143 ****
  /* non-export function prototypes */
  static void DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
           char *delim, char *null_print, bool csv_mode, char *quote,
!          char *escape, List *force_quote_atts, bool fe_copy);
  static void CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
   char *delim, char *null_print, bool csv_mode, char *quote, char *escape,
!        List *force_quote_atts);
  static void CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
   char *delim, char *null_print, bool csv_mode, char *quote, char *escape,
!          List *force_notnull_atts);
  static bool CopyReadLine(char * quote, char * escape);
  static char *CopyReadAttribute(const char *delim, const char *null_print,
                    CopyReadResult *result, bool *isnull);
--- 131,143 ----
  /* non-export function prototypes */
  static void DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
           char *delim, char *null_print, bool csv_mode, char *quote,
!          char *escape, List *force_quote_atts, bool header_line, bool fe_copy);
  static void CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
   char *delim, char *null_print, bool csv_mode, char *quote, char *escape,
!        List *force_quote_atts, bool header_line);
  static void CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
   char *delim, char *null_print, bool csv_mode, char *quote, char *escape,
!          List *force_notnull_atts, bool header_line);
  static bool CopyReadLine(char * quote, char * escape);
  static char *CopyReadAttribute(const char *delim, const char *null_print,
                    CopyReadResult *result, bool *isnull);
***************
*** 695,700 ****
--- 695,701 ----
      bool        binary = false;
      bool        oids = false;
      bool        csv_mode = false;
+     bool        header_line = false;
      char       *delim = NULL;
      char       *quote = NULL;
      char       *escape = NULL;
***************
*** 752,757 ****
--- 753,766 ----
                           errmsg("conflicting or redundant options")));
              csv_mode = intVal(defel->arg);
          }
+         else if (strcmp(defel->defname, "header") == 0)
+         {
+             if (header_line)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             header_line = intVal(defel->arg);
+         }
          else if (strcmp(defel->defname, "quote") == 0)
          {
              if (quote)
***************
*** 828,833 ****
--- 837,850 ----
                   errmsg("COPY delimiter must be a single character")));

      /*
+      * Check header
+      */
+     if (!csv_mode && header_line)
+         ereport(ERROR,
+                 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+                  errmsg("COPY HEADER available only in CSV mode")));
+
+     /*
       * Check quote
       */
      if (!csv_mode && quote != NULL)
***************
*** 1041,1047 ****
              }
          }
          CopyFrom(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
!                  quote, escape, force_notnull_atts);
      }
      else
      {                            /* copy from database to file */
--- 1058,1064 ----
              }
          }
          CopyFrom(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
!                  quote, escape, force_notnull_atts, header_line);
      }
      else
      {                            /* copy from database to file */
***************
*** 1105,1111 ****
          }

          DoCopyTo(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
!                  quote, escape, force_quote_atts, fe_copy);
      }

      if (!pipe)
--- 1122,1128 ----
          }

          DoCopyTo(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
!                  quote, escape, force_quote_atts, header_line, fe_copy);
      }

      if (!pipe)
***************
*** 1137,1143 ****
  static void
  DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
           char *delim, char *null_print, bool csv_mode, char *quote,
!          char *escape, List *force_quote_atts, bool fe_copy)
  {
      PG_TRY();
      {
--- 1154,1160 ----
  static void
  DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
           char *delim, char *null_print, bool csv_mode, char *quote,
!          char *escape, List *force_quote_atts, bool header_line, bool fe_copy)
  {
      PG_TRY();
      {
***************
*** 1145,1151 ****
              SendCopyBegin(binary, list_length(attnumlist));

          CopyTo(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
!                quote, escape, force_quote_atts);

          if (fe_copy)
              SendCopyEnd(binary);
--- 1162,1168 ----
              SendCopyBegin(binary, list_length(attnumlist));

          CopyTo(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
!                quote, escape, force_quote_atts, header_line);

          if (fe_copy)
              SendCopyEnd(binary);
***************
*** 1169,1175 ****
  static void
  CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
         char *delim, char *null_print, bool csv_mode, char *quote,
!        char *escape, List *force_quote_atts)
  {
      HeapTuple    tuple;
      TupleDesc    tupDesc;
--- 1186,1192 ----
  static void
  CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
         char *delim, char *null_print, bool csv_mode, char *quote,
!        char *escape, List *force_quote_atts, bool header_line)
  {
      HeapTuple    tuple;
      TupleDesc    tupDesc;
***************
*** 1257,1262 ****
--- 1274,1305 ----
              null_print = (char *)
                  pg_server_to_client((unsigned char *) null_print,
                                      strlen(null_print));
+
+         /*
+          * if a header has been requested send the line
+          */
+         if (header_line)
+         {
+             bool hdr_delim = false;
+             char * colname;
+
+             foreach(cur, attnumlist)
+             {
+                 int            attnum = lfirst_int(cur);
+
+                 if (hdr_delim)
+                     CopySendChar(delim[0]);
+                 hdr_delim = true;
+
+                 colname = NameStr(attr[attnum - 1]->attname);
+
+                 CopyAttributeOutCSV(colname, delim, quote, escape,
+                                     strcmp(colname, null_print) == 0 );
+             }
+
+             CopySendEndOfRow(binary);
+
+         }
      }

      scandesc = heap_beginscan(rel, ActiveSnapshot, 0, NULL);
***************
*** 1463,1469 ****
  static void
  CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
           char *delim, char *null_print, bool csv_mode, char *quote,
!          char *escape, List *force_notnull_atts)
  {
      HeapTuple    tuple;
      TupleDesc    tupDesc;
--- 1506,1512 ----
  static void
  CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
           char *delim, char *null_print, bool csv_mode, char *quote,
!          char *escape, List *force_notnull_atts, bool header_line)
  {
      HeapTuple    tuple;
      TupleDesc    tupDesc;
***************
*** 1690,1695 ****
--- 1733,1747 ----
      errcontext.previous = error_context_stack;
      error_context_stack = &errcontext;

+     /*
+      * on input just throw the header line away
+      */
+     if (header_line)
+     {
+         copy_lineno++;
+         done = CopyReadLine(quote, escape) ;
+     }
+
      while (!done)
      {
          bool        skip_tuple;
Index: src/backend/parser/gram.y
===================================================================
RCS file: /home/cvsmirror/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.483
diff -c -r2.483 gram.y
*** src/backend/parser/gram.y    2 Feb 2005 06:36:01 -0000    2.483
--- src/backend/parser/gram.y    13 Mar 2005 23:25:50 -0000
***************
*** 357,363 ****

      GLOBAL GRANT GROUP_P

!     HANDLER HAVING HOLD HOUR_P

      ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
      INDEX INHERITS INITIALLY INNER_P INOUT INPUT_P
--- 357,363 ----

      GLOBAL GRANT GROUP_P

!     HANDLER HAVING HEADER HOLD HOUR_P

      ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
      INDEX INHERITS INITIALLY INNER_P INOUT INPUT_P
***************
*** 1438,1443 ****
--- 1438,1447 ----
                  {
                      $$ = makeDefElem("csv", (Node *)makeInteger(TRUE));
                  }
+             | HEADER
+                 {
+                     $$ = makeDefElem("header", (Node *)makeInteger(TRUE));
+                 }
              | QUOTE opt_as Sconst
                  {
                      $$ = makeDefElem("quote", (Node *)makeString($3));
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /home/cvsmirror/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.154
diff -c -r1.154 keywords.c
*** src/backend/parser/keywords.c    31 Dec 2004 22:00:27 -0000    1.154
--- src/backend/parser/keywords.c    13 Mar 2005 23:25:50 -0000
***************
*** 148,153 ****
--- 148,154 ----
      {"group", GROUP_P},
      {"handler", HANDLER},
      {"having", HAVING},
+     {"header", HEADER},
      {"hold", HOLD},
      {"hour", HOUR_P},
      {"ilike", ILIKE},
Index: src/bin/psql/copy.c
===================================================================
RCS file: /home/cvsmirror/pgsql/src/bin/psql/copy.c,v
retrieving revision 1.56
diff -c -r1.56 copy.c
*** src/bin/psql/copy.c    22 Feb 2005 04:40:54 -0000    1.56
--- src/bin/psql/copy.c    13 Mar 2005 23:25:50 -0000
***************
*** 66,71 ****
--- 66,72 ----
      bool        binary;
      bool        oids;
      bool        csv_mode;
+     bool        header;
      char       *delim;
      char       *null;
      char       *quote;
***************
*** 289,294 ****
--- 290,297 ----
                  result->oids = true;
              else if (pg_strcasecmp(token, "csv") == 0)
                  result->csv_mode = true;
+             else if (pg_strcasecmp(token, "header") == 0)
+                 result->header = true;
              else if (pg_strcasecmp(token, "delimiter") == 0)
              {
                  token = strtokx(NULL, whitespace, NULL, "'",
***************
*** 481,486 ****
--- 484,492 ----
      if (options->csv_mode)
          appendPQExpBuffer(&query, " CSV");

+     if (options->header)
+         appendPQExpBuffer(&query, " HEADER");
+
      if (options->quote)
      {
          if (options->quote[0] == '\'')