Re: [HACKERS] psql \copy warning - Mailing list pgsql-patches

Jeremy Drake wrote:
> I use the \copy command from psql to load data into postgres.  I was
> fiddling with setting up a database on a HEAD build, and I got the
> following new warning
>
> testy=# \copy episodes from 'episodes.data' with delimiter as '\t'
> WARNING:  nonstandard use of escape in a string literal
> LINE 1: COPY episodes FROM STDIN USING DELIMITERS '\t'
>                                                   ^
> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
>
>
> I figured that this is the new standards conforming strings feature, and I
> guess I should get used to the new escape syntax.  So I tried the hint
>
> testy=# \copy episodes FROM 'episodes.data' with delimiter as E'\t'
> \copy: parse error at "'\t'"
>
> So is it just me, or is this decidedly non-helpful?  I assume someone
> missed this place for the new syntax tweaks?

Interesting bug report.  The basic question is whether \copy should
follow the quoting rules of the SQL server, or of psql.  Most psql
arguments have backslashes enabled, so I am thinking \copy should as
well, and not match COPY if standard_compliant_strings is on.

The attached patch fixes the warning you received by adding E'' strings
to the \copy arguments, and adds it for the other backslash commands
like \d.

I don't think we want a psql escape control setting, nor do we want to
remove backslash controls from psql commands (they are not SQL
standard).

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: src/bin/psql/command.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.166
diff -c -c -r1.166 command.c
*** src/bin/psql/command.c    2 Apr 2006 20:08:22 -0000    1.166
--- src/bin/psql/command.c    28 May 2006 03:22:10 -0000
***************
*** 681,688 ****
                  PGresult   *res;

                  initPQExpBuffer(&buf);
!                 printfPQExpBuffer(&buf, "ALTER USER %s PASSWORD '%s';",
!                                   fmtId(user), encrypted_password);
                  res = PSQLexec(buf.data, false);
                  termPQExpBuffer(&buf);
                  if (!res)
--- 681,689 ----
                  PGresult   *res;

                  initPQExpBuffer(&buf);
!                 printfPQExpBuffer(&buf, "ALTER USER %s PASSWORD %c'%s';",
!                                   fmtId(user), NEED_E_STR(encrypted_password),
!                                   encrypted_password);
                  res = PSQLexec(buf.data, false);
                  termPQExpBuffer(&buf);
                  if (!res)
Index: src/bin/psql/common.h
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/common.h,v
retrieving revision 1.47
diff -c -c -r1.47 common.h
*** src/bin/psql/common.h    6 Mar 2006 19:49:20 -0000    1.47
--- src/bin/psql/common.h    28 May 2006 03:22:10 -0000
***************
*** 22,27 ****
--- 22,29 ----

  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))

+ #define    NEED_E_STR(str)        (strchr(str, '\\') ? ESCAPE_STRING_SYNTAX : ' ')
+
  /*
   * Safer versions of some standard C library functions. If an
   * out-of-memory condition occurs, these functions will bail out
Index: src/bin/psql/copy.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/copy.c,v
retrieving revision 1.61
diff -c -c -r1.61 copy.c
*** src/bin/psql/copy.c    26 May 2006 19:51:29 -0000    1.61
--- src/bin/psql/copy.c    28 May 2006 03:22:10 -0000
***************
*** 462,481 ****
      if (options->delim)
      {
          if (options->delim[0] == '\'')
!             appendPQExpBuffer(&query, " USING DELIMITERS %s",
!                               options->delim);
          else
!             appendPQExpBuffer(&query, " USING DELIMITERS '%s'",
!                               options->delim);
      }

      /* There is no backward-compatible CSV syntax */
      if (options->null)
      {
          if (options->null[0] == '\'')
!             appendPQExpBuffer(&query, " WITH NULL AS %s", options->null);
          else
!             appendPQExpBuffer(&query, " WITH NULL AS '%s'", options->null);
      }

      if (options->csv_mode)
--- 462,483 ----
      if (options->delim)
      {
          if (options->delim[0] == '\'')
!             appendPQExpBuffer(&query, " USING DELIMITERS %c%s",
!                               NEED_E_STR(options->delim), options->delim);
          else
!             appendPQExpBuffer(&query, " USING DELIMITERS %c'%s'",
!                               NEED_E_STR(options->delim), options->delim);
      }

      /* There is no backward-compatible CSV syntax */
      if (options->null)
      {
          if (options->null[0] == '\'')
!             appendPQExpBuffer(&query, " WITH NULL AS %c%s",
!                               NEED_E_STR(options->null), options->null);
          else
!             appendPQExpBuffer(&query, " WITH NULL AS %c'%s'",
!                               NEED_E_STR(options->null), options->null);
      }

      if (options->csv_mode)
***************
*** 487,503 ****
      if (options->quote)
      {
          if (options->quote[0] == '\'')
!             appendPQExpBuffer(&query, " QUOTE AS %s", options->quote);
          else
!             appendPQExpBuffer(&query, " QUOTE AS '%s'", options->quote);
      }

      if (options->escape)
      {
          if (options->escape[0] == '\'')
!             appendPQExpBuffer(&query, " ESCAPE AS %s", options->escape);
          else
!             appendPQExpBuffer(&query, " ESCAPE AS '%s'", options->escape);
      }

      if (options->force_quote_list)
--- 489,509 ----
      if (options->quote)
      {
          if (options->quote[0] == '\'')
!             appendPQExpBuffer(&query, " QUOTE AS %c%s",
!                               NEED_E_STR(options->quote), options->quote);
          else
!             appendPQExpBuffer(&query, " QUOTE AS %c'%s'",
!                               NEED_E_STR(options->quote), options->quote);
      }

      if (options->escape)
      {
          if (options->escape[0] == '\'')
!             appendPQExpBuffer(&query, " ESCAPE AS %c%s",
!                               NEED_E_STR(options->escape), options->escape);
          else
!             appendPQExpBuffer(&query, " ESCAPE AS %c'%s'",
!                               NEED_E_STR(options->escape), options->escape);
      }

      if (options->force_quote_list)
Index: src/bin/psql/describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.136
diff -c -c -r1.136 describe.c
*** src/bin/psql/describe.c    28 May 2006 02:27:08 -0000    1.136
--- src/bin/psql/describe.c    28 May 2006 03:22:11 -0000
***************
*** 1902,1915 ****
              WHEREAND();
              if (altnamevar)
                  appendPQExpBuffer(buf,
!                                   "(%s ~ '^%s'\n"
!                                   "        OR %s ~ '^%s')\n",
!                                   namevar, namebuf.data,
!                                   altnamevar, namebuf.data);
              else
                  appendPQExpBuffer(buf,
!                                   "%s ~ '^%s'\n",
!                                   namevar, namebuf.data);
          }
      }

--- 1902,1917 ----
              WHEREAND();
              if (altnamevar)
                  appendPQExpBuffer(buf,
!                                   "(%s ~ %c'^%s'\n"
!                                   "        OR %s ~ %c'^%s')\n",
!                                   namevar, NEED_E_STR(namebuf.data),
!                                   namebuf.data, altnamevar,
!                                   NEED_E_STR(namebuf.data), namebuf.data);
              else
                  appendPQExpBuffer(buf,
!                                   "%s ~ %c'^%s'\n",
!                                   namevar, NEED_E_STR(namebuf.data),
!                                   namebuf.data);
          }
      }

***************
*** 1926,1933 ****
          if (schemabuf.data[0] && schemavar)
          {
              WHEREAND();
!             appendPQExpBuffer(buf, "%s ~ '^%s'\n",
!                               schemavar, schemabuf.data);
          }
      }
      else
--- 1928,1936 ----
          if (schemabuf.data[0] && schemavar)
          {
              WHEREAND();
!             appendPQExpBuffer(buf, "%s ~ %c'^%s'\n",
!                               schemavar, NEED_E_STR(schemabuf.data),
!                               schemabuf.data);
          }
      }
      else