Current-stream read for psql's \copy

From: mfeit+postgresql(at)notonthe(dot)net (Mark Feit)
To: pgsql-patches(at)postgresql(dot)org
Subject: Current-stream read for psql's \copy
Date: 2004-01-09 22:19:29
Message-ID: 16383.10481.63562.988332@gargle.gargle.HOWL
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-01-09 22:40:49 Re: Translations in the distributions
Previous Message Peter Eisentraut 2004-01-09 22:04:16 Re: Encoding problems in PostgreSQL with XML data

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2004-01-09 23:10:53 Re: [PATCHES] fork/exec patch: pre-CreateProcess finalization
Previous Message Bruce Momjian 2004-01-09 21:25:32 Re: fork/exec patch: CreateProcess calls for Win32