Greg Smith wrote:
I haven't heard anything from Andrew about ragged CVS import either.
I think that ultimately those features are useful, but just exceed
what the existing code could be hacked to handle cleanly.
The patch is attached for your edification/amusement. I have backpatched
it to 8.4 for the client that needed it, and it's working just fine. I
didn't pursue it when it was clear that it was not going to be accepted.
COPY returning text[] would allow us to achieve the same thing, a bit
more verbosely, but it would be a lot more work to develop.
cheers
andrew
Index: src/backend/commands/copy.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/copy.c,v
retrieving revision 1.316
diff -c -r1.316 copy.c
*** src/backend/commands/copy.c 29 Jul 2009 20:56:18 -0000 1.316
--- src/backend/commands/copy.c 13 Sep 2009 02:57:16 -0000
***************
*** 116,121 ****
--- 116,122 ----
char *escape; /* CSV escape char (must be 1 byte) */
bool *force_quote_flags; /* per-column CSV FQ flags */
bool *force_notnull_flags; /* per-column CSV FNN flags */
+ bool ragged; /* allow ragged CSV input? */
/* these are just for error messages, see copy_in_error_callback */
const char *cur_relname; /* table name for error messages */
***************
*** 822,827 ****
--- 823,836 ----
errmsg("conflicting or redundant options")));
force_notnull = (List *) defel->arg;
}
+ else if (strcmp(defel->defname, "ragged") == 0)
+ {
+ if (cstate->ragged)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ cstate->ragged = intVal(defel->arg);
+ }
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
***************
*** 948,953 ****
--- 957,972 ----
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("COPY force not null only available using COPY FROM")));
+ /* Check ragged */
+ if (!cstate->csv_mode && cstate->ragged)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY ragged available only in CSV mode")));
+ if (cstate->ragged && !is_from)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("COPY ragged only available using COPY FROM")));
+
/* Don't allow the delimiter to appear in the null string. */
if (strchr(cstate->null_print, cstate->delim[0]) != NULL)
ereport(ERROR,
***************
*** 2951,2964 ****
int input_len;
/* Make sure space remains in fieldvals[] */
! if (fieldno >= maxfields)
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
errmsg("extra data after last expected column")));
/* Remember start of field on both input and output sides */
start_ptr = cur_ptr;
! fieldvals[fieldno] = output_ptr;
/*
* Scan data for field,
--- 2970,2984 ----
int input_len;
/* Make sure space remains in fieldvals[] */
! if (fieldno >= maxfields && ! cstate->ragged)
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
errmsg("extra data after last expected column")));
/* Remember start of field on both input and output sides */
start_ptr = cur_ptr;
! if (fieldno < maxfields)
! fieldvals[fieldno] = output_ptr;
/*
* Scan data for field,
***************
*** 3045,3051 ****
/* Check whether raw input matched null marker */
input_len = end_ptr - start_ptr;
if (!saw_quote && input_len == cstate->null_print_len &&
! strncmp(start_ptr, cstate->null_print, input_len) == 0)
fieldvals[fieldno] = NULL;
fieldno++;
--- 3065,3072 ----
/* Check whether raw input matched null marker */
input_len = end_ptr - start_ptr;
if (!saw_quote && input_len == cstate->null_print_len &&
! strncmp(start_ptr, cstate->null_print, input_len) == 0 &&
! fieldno < maxfields)
fieldvals[fieldno] = NULL;
fieldno++;
***************
*** 3059,3065 ****
Assert(*output_ptr == '\0');
cstate->attribute_buf.len = (output_ptr - cstate->attribute_buf.data);
! return fieldno;
}
--- 3080,3092 ----
Assert(*output_ptr == '\0');
cstate->attribute_buf.len = (output_ptr - cstate->attribute_buf.data);
! /* for ragged input, set field null for underflowed fields */
! if (cstate->ragged)
! while (fieldno < maxfields)
! fieldvals[fieldno++] = NULL;
!
!
! return cstate->ragged ? maxfields : fieldno;
}
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.677
diff -c -r2.677 gram.y
*** src/backend/parser/gram.y 18 Aug 2009 23:40:20 -0000 2.677
--- src/backend/parser/gram.y 13 Sep 2009 02:57:17 -0000
***************
*** 504,510 ****
QUOTE
! RANGE READ REAL REASSIGN RECHECK RECURSIVE REFERENCES REINDEX
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
--- 504,510 ----
QUOTE
! RAGGED RANGE READ REAL REASSIGN RECHECK RECURSIVE REFERENCES REINDEX
RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA RESET RESTART
RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE
***************
*** 2050,2055 ****
--- 2050,2059 ----
{
$$ = makeDefElem("force_notnull", (Node *)$4);
}
+ | RAGGED
+ {
+ $$ = makeDefElem("ragged",(Node *)makeInteger(TRUE));
+ }
;
/* The following exist for backward compatibility */
***************
*** 10373,10378 ****
--- 10377,10383 ----
| PROCEDURAL
| PROCEDURE
| QUOTE
+ | RAGGED
| RANGE
| READ
| REASSIGN
Index: src/bin/psql/copy.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/copy.c,v
retrieving revision 1.82
diff -c -r1.82 copy.c
*** src/bin/psql/copy.c 7 Aug 2009 20:16:11 -0000 1.82
--- src/bin/psql/copy.c 13 Sep 2009 02:57:17 -0000
***************
*** 34,40 ****
* The documented syntax is:
* \copy tablename [(columnlist)] from|to filename
* [ with ] [ binary ] [ oids ] [ delimiter [as] char ] [ null [as] string ]
! * [ csv [ header ] [ quote [ AS ] string ] escape [as] string
* [ force not null column [, ...] | force quote column [, ...] | * ] ]
*
* \copy ( select stmt ) to filename
--- 34,40 ----
* The documented syntax is:
* \copy tablename [(columnlist)] from|to filename
* [ with ] [ binary ] [ oids ] [ delimiter [as] char ] [ null [as] string ]
! * [ csv [ header ] [ quote [ AS ] string ] escape [as] string [ ragged ]
* [ force not null column [, ...] | force quote column [, ...] | * ] ]
*
* \copy ( select stmt ) to filename
***************
*** 69,74 ****
--- 69,75 ----
char *escape;
char *force_quote_list;
char *force_notnull_list;
+ bool ragged;
};
***************
*** 268,273 ****
--- 269,276 ----
result->csv_mode = true;
else if (pg_strcasecmp(token, "header") == 0)
result->header = true;
+ else if (pg_strcasecmp(token, "ragged") == 0)
+ result->ragged = true;
else if (pg_strcasecmp(token, "delimiter") == 0)
{
if (result->delim)
***************
*** 477,482 ****
--- 480,488 ----
if (options->header)
appendPQExpBuffer(&query, " HEADER");
+ if (options->ragged)
+ appendPQExpBuffer(&query, " RAGGED");
+
if (options->quote)
emit_copy_option(&query, " QUOTE AS ", options->quote);
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v
retrieving revision 1.185
diff -c -r1.185 tab-complete.c
*** src/bin/psql/tab-complete.c 2 Aug 2009 22:14:52 -0000 1.185
--- src/bin/psql/tab-complete.c 13 Sep 2009 02:57:18 -0000
***************
*** 1249,1255 ****
pg_strcasecmp(prev3_wd, "TO") == 0))
{
static const char *const list_CSV[] =
! {"HEADER", "QUOTE", "ESCAPE", "FORCE QUOTE", NULL};
COMPLETE_WITH_LIST(list_CSV);
}
--- 1249,1255 ----
pg_strcasecmp(prev3_wd, "TO") == 0))
{
static const char *const list_CSV[] =
! {"HEADER", "QUOTE", "ESCAPE", "FORCE QUOTE", "RAGGED", NULL};
COMPLETE_WITH_LIST(list_CSV);
}
Index: src/include/parser/kwlist.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/parser/kwlist.h,v
retrieving revision 1.2
diff -c -r1.2 kwlist.h
*** src/include/parser/kwlist.h 6 Apr 2009 08:42:53 -0000 1.2
--- src/include/parser/kwlist.h 13 Sep 2009 02:57:18 -0000
***************
*** 294,299 ****
--- 294,300 ----
PG_KEYWORD("procedural", PROCEDURAL, UNRESERVED_KEYWORD)
PG_KEYWORD("procedure", PROCEDURE, UNRESERVED_KEYWORD)
PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("ragged", RAGGED, UNRESERVED_KEYWORD)
PG_KEYWORD("range", RANGE, UNRESERVED_KEYWORD)
PG_KEYWORD("read", READ, UNRESERVED_KEYWORD)
PG_KEYWORD("real", REAL, COL_NAME_KEYWORD)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
