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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
