Re: [update] Re: Patch to bring \copy syntax more in line with SQL copy

Bill Moran <wmoran(at)potentialtech(dot)com> writes:
> As far as I can tell, this should make the \copy syntax equivalent to the
> SQL copy syntax (as described in the docs) while still maintaining
> backward compatibility with older syntaxes.

On reviewing this, I see that the existing code is much closer to what
the backend actually accepts (see backend/parser/gram.y) than your
proposed change. As far as I can see, the only bugs in the psql code
are that it doesn't treat USING and WITH as optional in the right
places, and it doesn't allow OIDS in the WITH loop. I've applied the
attached patch to bring psql into exact agreement with what the backend
actually does.

> The second is against doc/src/sgml/ref/psql-ref.sgml, and only changes the
> explaination of the \copy syntax.

psql-ref presently documents only the "preferred" syntax and not any of
the backwards-compatibility options. I'm inclined to leave it alone.
If we do want to be more complete we should probably do what the COPY
reference page does, which is to describe the old syntax separately.

regards, tom lane

*** src/bin/psql/copy.c.orig Mon Jan 26 17:35:32 2004
--- src/bin/psql/copy.c Wed Jan 28 17:10:13 2004
***************
*** 36,46 ****
* parse_slash_copy
* -- parses \copy command line
*
! * Accepted syntax: \copy table [(columnlist)] [with oids] from|to filename [with ] [ oids ] [ delimiter char] [ null as string ]
* (binary is not here yet)
*
! * Old syntax for backward compatibility: (2002-06-19):
! * \copy table [(columnlist)] [with oids] from|to filename [ using delimiters char] [ with null as string ]
*
* table name can be double-quoted and can have a schema part.
* column names can be double-quoted.
--- 36,54 ----
* parse_slash_copy
* -- parses \copy command line
*
! * The documented preferred syntax is:
! * \copy tablename [(columnlist)] from|to filename
! * [ with ] [ oids ] [ delimiter [as] char ] [ null [as] string ]
* (binary is not here yet)
*
! * The pre-7.3 syntax was:
! * \copy tablename [(columnlist)] [with oids] from|to filename
! * [ [using] delimiters char ] [ with null as string ]
! *
! * The actual accepted syntax is a rather unholy combination of these,
! * plus some undocumented flexibility (for instance, the clauses after
! * WITH can appear in any order). The accepted syntax matches what
! * the backend grammar actually accepts (see backend/parser/gram.y).
*
* table name can be double-quoted and can have a schema part.
* column names can be double-quoted.
***************
*** 243,248 ****
--- 251,259 ----
0, false, pset.encoding);
if (!(token && strcasecmp(token, "delimiters") == 0))
goto error;
+ }
+ if (token && strcasecmp(token, "delimiters") == 0)
+ {
token = strtokx(NULL, whitespace, NULL, "'",
'\\', false, pset.encoding);
if (!token)
***************
*** 254,265 ****

if (token)
{
! if (strcasecmp(token, "with") != 0)
! goto error;
! while ((token = strtokx(NULL, whitespace, NULL, NULL,
! 0, false, pset.encoding)) != NULL)
{
! if (strcasecmp(token, "delimiter") == 0)
{
token = strtokx(NULL, whitespace, NULL, "'",
'\\', false, pset.encoding);
--- 265,286 ----

if (token)
{
! /*
! * WITH is optional. Also, the backend will allow WITH followed by
! * nothing, so we do too.
! */
! if (strcasecmp(token, "with") == 0)
! token = strtokx(NULL, whitespace, NULL, NULL,
! 0, false, pset.encoding);
!
! while (token)
{
! /* someday allow BINARY here */
! if (strcasecmp(token, "oids") == 0)
! {
! result->oids = true;
! }
! else if (strcasecmp(token, "delimiter") == 0)
{
token = strtokx(NULL, whitespace, NULL, "'",
'\\', false, pset.encoding);
***************
*** 285,290 ****
--- 306,314 ----
}
else
goto error;
+
+ token = strtokx(NULL, whitespace, NULL, NULL,
+ 0, false, pset.encoding);
}
}