Skip to content

Commit b63990c

Browse files
bmomjianbmomjian
committed
Add COPY WITH CVS HEADER to allow a heading line as the first line in
COPY. Andrew Dunstan
1 parent ce1ab39 commit b63990c

File tree

6 files changed

+92
-20
lines changed

6 files changed

+92
-20
lines changed

‎doc/src/sgml/ref/copy.sgml

Lines changed: 18 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.64 2005/05/06 03:38:05 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.65 2005/05/07 02:22:45 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -24,22 +24,25 @@ PostgreSQL documentation
2424
COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
2525
FROM { '<replaceable class="parameter">filename</replaceable>' | STDIN }
2626
[ [ WITH ]
27-
[ BINARY ]
27+
[ BINARY ]
2828
[ OIDS ]
2929
[ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ]
3030
[ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ]
31-
[ CSV [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ]
31+
[ CSV [ HEADER ]
32+
[ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ]
3233
[ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ]
3334
[ FORCE NOT NULL <replaceable class="parameter">column</replaceable> [, ...] ]
3435

3536
COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ]
3637
TO { '<replaceable class="parameter">filename</replaceable>' | STDOUT }
3738
[ [ WITH ]
3839
[ BINARY ]
40+
[ HEADER ]
3941
[ OIDS ]
4042
[ DELIMITER [ AS ] '<replaceable class="parameter">delimiter</replaceable>' ]
4143
[ NULL [ AS ] '<replaceable class="parameter">null string</replaceable>' ]
42-
[ CSV [ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ]
44+
[ CSV [ HEADER ]
45+
[ QUOTE [ AS ] '<replaceable class="parameter">quote</replaceable>' ]
4346
[ ESCAPE [ AS ] '<replaceable class="parameter">escape</replaceable>' ]
4447
[ FORCE QUOTE <replaceable class="parameter">column</replaceable> [, ...] ]
4548
</synopsis>
@@ -191,6 +194,17 @@ COPY <replaceable class="parameter">tablename</replaceable> [ ( <replaceable cla
191194
</listitem>
192195
</varlistentry>
193196

197+
<varlistentry>
198+
<term><literal>HEADER</literal></term>
199+
<listitem>
200+
<para>
201+
Specifies the file contains a header line with the names of each
202+
column in the file. On output, the first line contains the column
203+
names from the table, and on input, the first line is ignored.
204+
</para>
205+
</listitem>
206+
</varlistentry>
207+
194208
<varlistentry>
195209
<term><replaceable class="parameter">quote</replaceable></term>
196210
<listitem>

‎src/backend/commands/copy.c

Lines changed: 56 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.243 2005/05/06 17:24:53 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.244 2005/05/07 02:22:46 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -131,13 +131,13 @@ static bool line_buf_converted;
131131
/* non-export function prototypes */
132132
static void DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
133133
char *delim, char *null_print, bool csv_mode, char *quote,
134-
char *escape, List *force_quote_atts, bool fe_copy);
134+
char *escape, List *force_quote_atts, bool header_line, bool fe_copy);
135135
static void CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
136136
char *delim, char *null_print, bool csv_mode, char *quote, char *escape,
137-
List *force_quote_atts);
137+
List *force_quote_atts, bool header_line);
138138
static void CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
139139
char *delim, char *null_print, bool csv_mode, char *quote, char *escape,
140-
List *force_notnull_atts);
140+
List *force_notnull_atts, bool header_line);
141141
static bool CopyReadLine(char * quote, char * escape);
142142
static char *CopyReadAttribute(const char *delim, const char *null_print,
143143
CopyReadResult *result, bool *isnull);
@@ -695,6 +695,7 @@ DoCopy(const CopyStmt *stmt)
695695
bool binary = false;
696696
bool oids = false;
697697
bool csv_mode = false;
698+
bool header_line = false;
698699
char *delim = NULL;
699700
char *quote = NULL;
700701
char *escape = NULL;
@@ -752,6 +753,14 @@ DoCopy(const CopyStmt *stmt)
752753
errmsg("conflicting or redundant options")));
753754
csv_mode = intVal(defel->arg);
754755
}
756+
else if (strcmp(defel->defname, "header") == 0)
757+
{
758+
if (header_line)
759+
ereport(ERROR,
760+
(errcode(ERRCODE_SYNTAX_ERROR),
761+
errmsg("conflicting or redundant options")));
762+
header_line = intVal(defel->arg);
763+
}
755764
else if (strcmp(defel->defname, "quote") == 0)
756765
{
757766
if (quote)
@@ -825,6 +834,12 @@ DoCopy(const CopyStmt *stmt)
825834
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
826835
errmsg("COPY delimiter must be a single character")));
827836

837+
/* Check header */
838+
if (!csv_mode && header_line)
839+
ereport(ERROR,
840+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
841+
errmsg("COPY HEADER available only in CSV mode")));
842+
828843
/* Check quote */
829844
if (!csv_mode && quote != NULL)
830845
ereport(ERROR,
@@ -1015,7 +1030,7 @@ DoCopy(const CopyStmt *stmt)
10151030
}
10161031
}
10171032
CopyFrom(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
1018-
quote, escape, force_notnull_atts);
1033+
quote, escape, force_notnull_atts, header_line);
10191034
}
10201035
else
10211036
{ /* copy from database to file */
@@ -1079,7 +1094,7 @@ DoCopy(const CopyStmt *stmt)
10791094
}
10801095

10811096
DoCopyTo(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
1082-
quote, escape, force_quote_atts, fe_copy);
1097+
quote, escape, force_quote_atts, header_line, fe_copy);
10831098
}
10841099

10851100
if (!pipe)
@@ -1111,15 +1126,15 @@ DoCopy(const CopyStmt *stmt)
11111126
static void
11121127
DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
11131128
char *delim, char *null_print, bool csv_mode, char *quote,
1114-
char *escape, List *force_quote_atts, bool fe_copy)
1129+
char *escape, List *force_quote_atts, bool header_line, bool fe_copy)
11151130
{
11161131
PG_TRY();
11171132
{
11181133
if (fe_copy)
11191134
SendCopyBegin(binary, list_length(attnumlist));
11201135

11211136
CopyTo(rel, attnumlist, binary, oids, delim, null_print, csv_mode,
1122-
quote, escape, force_quote_atts);
1137+
quote, escape, force_quote_atts, header_line);
11231138

11241139
if (fe_copy)
11251140
SendCopyEnd(binary);
@@ -1143,7 +1158,7 @@ DoCopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
11431158
static void
11441159
CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
11451160
char *delim, char *null_print, bool csv_mode, char *quote,
1146-
char *escape, List *force_quote_atts)
1161+
char *escape, List *force_quote_atts, bool header_line)
11471162
{
11481163
HeapTuple tuple;
11491164
TupleDesc tupDesc;
@@ -1226,6 +1241,30 @@ CopyTo(Relation rel, List *attnumlist, bool binary, bool oids,
12261241
null_print = (char *)
12271242
pg_server_to_client((unsigned char *) null_print,
12281243
strlen(null_print));
1244+
1245+
/* if a header has been requested send the line */
1246+
if (header_line)
1247+
{
1248+
bool hdr_delim = false;
1249+
char *colname;
1250+
1251+
foreach(cur, attnumlist)
1252+
{
1253+
int attnum = lfirst_int(cur);
1254+
1255+
if (hdr_delim)
1256+
CopySendChar(delim[0]);
1257+
hdr_delim = true;
1258+
1259+
colname = NameStr(attr[attnum - 1]->attname);
1260+
1261+
CopyAttributeOutCSV(colname, delim, quote, escape,
1262+
strcmp(colname, null_print) == 0);
1263+
}
1264+
1265+
CopySendEndOfRow(binary);
1266+
1267+
}
12291268
}
12301269

12311270
scandesc = heap_beginscan(rel, ActiveSnapshot, 0, NULL);
@@ -1427,7 +1466,7 @@ limit_printout_length(StringInfo buf)
14271466
static void
14281467
CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
14291468
char *delim, char *null_print, bool csv_mode, char *quote,
1430-
char *escape, List *force_notnull_atts)
1469+
char *escape, List *force_notnull_atts, bool header_line)
14311470
{
14321471
HeapTuple tuple;
14331472
TupleDesc tupDesc;
@@ -1653,6 +1692,13 @@ CopyFrom(Relation rel, List *attnumlist, bool binary, bool oids,
16531692
errcontext.previous = error_context_stack;
16541693
error_context_stack = &errcontext;
16551694

1695+
/* on input just throw the header line away */
1696+
if (header_line)
1697+
{
1698+
copy_lineno++;
1699+
done = CopyReadLine(quote, escape) ;
1700+
}
1701+
16561702
while (!done)
16571703
{
16581704
bool skip_tuple;

‎src/backend/parser/gram.y

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.490 2005/05/06 03:42:17 momjian Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.491 2005/05/07 02:22:46 momjian Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -362,7 +362,7 @@ static void doNegateFloat(Value *v);
362362

363363
GLOBAL GRANT GROUP_P
364364

365-
HANDLER HAVING HOLD HOUR_P
365+
HANDLER HAVING HEADER HOLD HOUR_P
366366

367367
ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
368368
INDEX INHERITS INITIALLY INNER_P INOUT INPUT_P
@@ -1444,6 +1444,10 @@ copy_opt_item:
14441444
{
14451445
$$ = makeDefElem("csv", (Node *)makeInteger(TRUE));
14461446
}
1447+
| HEADER
1448+
{
1449+
$$ = makeDefElem("header", (Node *)makeInteger(TRUE));
1450+
}
14471451
| QUOTE opt_as Sconst
14481452
{
14491453
$$ = makeDefElem("quote", (Node *)makeString($3));
@@ -7787,6 +7791,7 @@ unreserved_keyword:
77877791
| FUNCTION
77887792
| GLOBAL
77897793
| HANDLER
7794+
| HEADER
77907795
| HOLD
77917796
| HOUR_P
77927797
| IMMEDIATE

‎src/backend/parser/keywords.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.154 2004/12/31 22:00:27 pgsql Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/keywords.c,v 1.155 2005/05/07 02:22:47 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -148,6 +148,7 @@ static const ScanKeyword ScanKeywords[] = {
148148
{"group", GROUP_P},
149149
{"handler", HANDLER},
150150
{"having", HAVING},
151+
{"header", HEADER},
151152
{"hold", HOLD},
152153
{"hour", HOUR_P},
153154
{"ilike", ILIKE},

‎src/bin/psql/copy.c

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 2000-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/bin/psql/copy.c,v 1.56 2005/02/22 04:40:54 momjian Exp $
6+
* $PostgreSQL: pgsql/src/bin/psql/copy.c,v 1.57 2005/05/07 02:22:49 momjian Exp $
77
*/
88
#include "postgres_fe.h"
99
#include "copy.h"
@@ -66,6 +66,7 @@ struct copy_options
6666
bool binary;
6767
bool oids;
6868
bool csv_mode;
69+
bool header;
6970
char *delim;
7071
char *null;
7172
char *quote;
@@ -289,6 +290,8 @@ parse_slash_copy(const char *args)
289290
result->oids = true;
290291
else if (pg_strcasecmp(token, "csv") == 0)
291292
result->csv_mode = true;
293+
else if (pg_strcasecmp(token, "header") == 0)
294+
result->header = true;
292295
else if (pg_strcasecmp(token, "delimiter") == 0)
293296
{
294297
token = strtokx(NULL, whitespace, NULL, "'",
@@ -481,6 +484,9 @@ do_copy(const char *args)
481484
if (options->csv_mode)
482485
appendPQExpBuffer(&query, " CSV");
483486

487+
if (options->header)
488+
appendPQExpBuffer(&query, " HEADER");
489+
484490
if (options->quote)
485491
{
486492
if (options->quote[0] == '\'')

‎src/bin/psql/tab-complete.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 2000-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.126 2005/05/04 14:25:24 tgl Exp $
6+
* $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.127 2005/05/07 02:22:49 momjian Exp $
77
*/
88

99
/*----------------------------------------------------------------------
@@ -1040,7 +1040,7 @@ psql_completion(char *text, int start, int end)
10401040
pg_strcasecmp(prev3_wd, "TO") == 0))
10411041
{
10421042
static const char *const list_CSV[] =
1043-
{"QUOTE", "ESCAPE", "FORCE QUOTE", NULL};
1043+
{"HEADER", "QUOTE", "ESCAPE", "FORCE QUOTE", NULL};
10441044

10451045
COMPLETE_WITH_LIST(list_CSV);
10461046
}

0 commit comments

Comments
 (0)