Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

pgsql-hackers by date

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

pgsql-patches by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group