Current-stream read for psql's \copy - Mailing list pgsql-patches

This patch against 7.4.1's psql and the documentation adds the option
of reading rows from the "current" input stream (standard input, -f
xxx, \i xxx) during a "\copy ... from" operation in psql.  The details
were proposed and discussed (somewhat) here:

  http://archives.postgresql.org/pgsql-hackers/2003-12/msg00687.php
  http://archives.postgresql.org/pgsql-hackers/2004-01/msg00056.php

After some consideration, I decided to stick with the
originally-proposed syntax because I couldn't come up with anything
that made as much sense.

This patch also includes a change which makes the "enter data to be
copied..." message appear for both \copy and COPY in an interactive
setting.

If there's interest, I can build a patch against the current
development version.

                            - Mark


---8<--- TRIM, PATCH AND ENJOY ------

Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.102
diff -e -c -r1.102 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml    23 Dec 2003 23:13:14 -0000    1.102
--- doc/src/sgml/ref/psql-ref.sgml    9 Jan 2004 21:50:09 -0000
***************
*** 705,711 ****
          <term><literal>\copy <replaceable class="parameter">table</replaceable>
      [ ( <replaceable class="parameter">column_list</replaceable> ) ]
          { <literal>from</literal> | <literal>to</literal> }
!     <replaceable class="parameter">filename</replaceable> | stdin | stdout
          [ <literal>with</literal> ]
              [ <literal>oids</literal> ]
              [ <literal>delimiter [as] </literal> '<replaceable class="parameter">character</replaceable>' ]
--- 705,711 ----
          <term><literal>\copy <replaceable class="parameter">table</replaceable>
      [ ( <replaceable class="parameter">column_list</replaceable> ) ]
          { <literal>from</literal> | <literal>to</literal> }
!     { <replaceable class="parameter">filename</replaceable> | stdin | stdout | - }
          [ <literal>with</literal> ]
              [ <literal>oids</literal> ]
              [ <literal>delimiter [as] </literal> '<replaceable class="parameter">character</replaceable>' ]
***************
*** 720,737 ****
          reading or writing the specified file,
          <application>psql</application> reads or writes the file and
          routes the data between the server and the local file system.
!     This means that file accessibility and privileges are those
!     of the local user, not the server, and no SQL superuser
!     privileges are required.
      </para>

      <para>
      The syntax of the command is similar to that of the
!     <acronym>SQL</acronym> <command>COPY</command> command.  (See its
!     description for the details.)  Note that, because of this,
      special parsing rules apply to the <command>\copy</command>
      command. In particular, the variable substitution rules and
      backslash escapes do not apply.
      </para>

          <tip>
--- 720,753 ----
          reading or writing the specified file,
          <application>psql</application> reads or writes the file and
          routes the data between the server and the local file system.
!         This means that file accessibility and privileges are those of
!         the local user, not the server, and no SQL superuser
!         privileges are required.
      </para>

      <para>
      The syntax of the command is similar to that of the
!     <acronym>SQL</acronym> <command>COPY</command> command.  (See
!     its description for the details.)  Note that, because of this,
      special parsing rules apply to the <command>\copy</command>
      command. In particular, the variable substitution rules and
      backslash escapes do not apply.
+     </para>
+
+         <para>
+     For <literal>\copy <replaceable
+     class="parameter">table</replaceable> from <replaceable
+     class="parameter">filename</replaceable></literal> operations,
+     <application>psql</application> adds the option of using a
+     hyphen instead of <replacable
+     class="parameter">filename</replacable>.  This causes
+     <literal>\copy</literal> to read rows from the stream that
+     issued the command, continuing until <literal>\.</literal> is
+     read or the stream reaches <acronym>EOF</>.  This option is
+     useful for populating tables in-line within a file being read
+     with the <option>-f</option> command line argument or the
+     <command>\i</command> command.  (See the note below about
+     <literal>stdin</literal> and <literal>stdout</literal>.)
      </para>

          <tip>
Index: src/bin/psql/common.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/common.c,v
retrieving revision 1.78
diff -e -c -r1.78 common.c
*** src/bin/psql/common.c    29 Nov 2003 19:52:06 -0000    1.78
--- src/bin/psql/common.c    9 Jan 2004 21:50:09 -0000
***************
*** 513,524 ****
              break;

          case PGRES_COPY_IN:
-             if (pset.cur_cmd_interactive && !QUIET())
-                 puts(gettext("Enter data to be copied followed by a newline.\n"
-                              "End with a backslash and a period on a line by itself."));
-
              success = handleCopyIn(pset.db, pset.cur_cmd_source,
!               pset.cur_cmd_interactive ? get_prompt(PROMPT_COPY) : NULL);
              break;

          default:
--- 513,520 ----
              break;

          case PGRES_COPY_IN:
              success = handleCopyIn(pset.db, pset.cur_cmd_source,
!                            (pset.cur_cmd_interactive ? get_prompt(PROMPT_COPY) : NULL), NULL);
              break;

          default:
Index: src/bin/psql/copy.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/copy.c,v
retrieving revision 1.35
diff -e -c -r1.35 copy.c
*** src/bin/psql/copy.c    1 Dec 2003 22:14:40 -0000    1.35
--- src/bin/psql/copy.c    9 Jan 2004 21:50:10 -0000
***************
*** 48,66 ****
   * returns a malloc'ed structure with the options, or NULL on parsing error
   */

- struct copy_options
- {
-     char       *table;
-     char       *column_list;
-     char       *file;            /* NULL = stdin/stdout */
-     bool        from;
-     bool        binary;
-     bool        oids;
-     char       *delim;
-     char       *null;
- };
-
-
  static void
  free_copy_options(struct copy_options * ptr)
  {
--- 48,53 ----
***************
*** 216,226 ****
      if (!token)
          goto error;

!     if (strcasecmp(token, "stdin") == 0 ||
!         strcasecmp(token, "stdout") == 0)
          result->file = NULL;
      else
!         result->file = xstrdup(token);

      token = strtokx(NULL, whitespace, NULL, NULL,
                      0, false, pset.encoding);
--- 203,226 ----
      if (!token)
          goto error;

!     if ( strcmp(token, "-") == 0 )
!     {
!         /* Can't do this on output */
!         if ( ! result->from )
!             goto error;
!
!         result->in_dash = true;
          result->file = NULL;
+     }
      else
!     {
!         result->in_dash = false;
!         if (strcasecmp(token, "stdin") == 0 ||
!             strcasecmp(token, "stdout") == 0)
!             result->file = NULL;
!         else
!             result->file = xstrdup(token);
!     }

      token = strtokx(NULL, whitespace, NULL, NULL,
                      0, false, pset.encoding);
***************
*** 362,368 ****
          if (options->file)
              copystream = fopen(options->file, "r");
          else
!             copystream = stdin;
      }
      else
      {
--- 362,370 ----
          if (options->file)
              copystream = fopen(options->file, "r");
          else
!              /* Use the current input source if requested, stdin otherwise. */
!              copystream = (options->in_dash ? pset.cur_cmd_source : stdin);
!
      }
      else
      {
***************
*** 400,406 ****
              success = handleCopyOut(pset.db, copystream);
              break;
          case PGRES_COPY_IN:
!             success = handleCopyIn(pset.db, copystream, NULL);
              break;
          case PGRES_NONFATAL_ERROR:
          case PGRES_FATAL_ERROR:
--- 402,408 ----
              success = handleCopyOut(pset.db, copystream);
              break;
          case PGRES_COPY_IN:
!             success = handleCopyIn(pset.db, copystream, NULL, options);
              break;
          case PGRES_NONFATAL_ERROR:
          case PGRES_FATAL_ERROR:
***************
*** 415,421 ****

      PQclear(result);

!     if (copystream != stdout && copystream != stdin)
          fclose(copystream);
      free_copy_options(options);
      return success;
--- 417,423 ----

      PQclear(result);

!      if (copystream != stdout && copystream != stdin && (! options->in_dash))
          fclose(copystream);
      free_copy_options(options);
      return success;
***************
*** 489,496 ****
   *     if stdin is an interactive tty)
   */

  bool
! handleCopyIn(PGconn *conn, FILE *copystream, const char *prompt)
  {
      bool        copydone = false;
      bool        firstload;
--- 491,510 ----
   *     if stdin is an interactive tty)
   */

+ static struct copy_options default_copy_options = {
+     NULL,        /* table */
+     NULL,        /* column_list */
+     NULL,        /* file */
+     0,        /* from */
+     0,        /* in_dash */
+     0,        /* binary */
+     0,        /* oids */
+     NULL,        /* delim */
+     NULL        /* null */
+ };
+
  bool
! handleCopyIn(PGconn *conn, FILE *copystream, const char *prompt, struct copy_options *options)
  {
      bool        copydone = false;
      bool        firstload;
***************
*** 502,512 ****
--- 516,535 ----
      int            ret;
      unsigned int linecount = 0;

+
+     if (options == NULL)
+         options = &default_copy_options;
+
      if (prompt)                    /* disable prompt if not interactive */
      {
          if (!isatty(fileno(copystream)))
              prompt = NULL;
      }
+
+     if (pset.cur_cmd_interactive && !QUIET())
+         puts(gettext("Enter data to be copied followed by a newline.\n"
+             "End with a backslash and a period on a line by itself."));
+

      while (!copydone)
      {                            /* for each input line ... */
Index: src/bin/psql/copy.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/copy.h,v
retrieving revision 1.14
diff -e -c -r1.14 copy.h
*** src/bin/psql/copy.h    29 Nov 2003 19:52:06 -0000    1.14
--- src/bin/psql/copy.h    9 Jan 2004 21:50:10 -0000
***************
*** 10,15 ****
--- 10,28 ----

  #include "libpq-fe.h"

+ struct copy_options
+ {
+     char       *table;
+     char       *column_list;
+     char       *file;            /* NULL = stdin/stdout/- */
+     bool        from;
+     bool        in_dash;
+     bool        binary;
+     bool        oids;
+     char       *delim;
+     char       *null;
+ };
+

  /* handler for \copy */
  bool        do_copy(const char *args);
***************
*** 17,22 ****
  /* lower level processors for copy in/out streams */

  bool        handleCopyOut(PGconn *conn, FILE *copystream);
! bool        handleCopyIn(PGconn *conn, FILE *copystream, const char *prompt);

  #endif
--- 30,35 ----
  /* lower level processors for copy in/out streams */

  bool        handleCopyOut(PGconn *conn, FILE *copystream);
! bool        handleCopyIn(PGconn *conn, FILE *copystream, const char *prompt, struct copy_options *options);

  #endif