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] == '\'')
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
