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

Fri, 9 Jan 2004 17:19:29 -0500
mfeit+postgresql@notonthe.net (Mark Feit)
Current-stream read for psql's \copy
mfeit+postgresql@notonthe.net (Mark Feit) writes:
> 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.

Applied with some editorialization (the prompting logic didn't work
right, and I didn't see the value of exporting struct copy_options).

			regards, tom lane

Tue, 20 Jan 2004 18:50:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Current-stream read for psql's \copy 
Mark Feit wrote:
> 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.

Actually, I am confused by our current \copy behavior.  Given the
following file:

	CREATE TABLE test(x INT);
	\copy test FROM STDIN
	444
	\.
	SELECT * FROM test;

'psql test </tmp/x' works fine, but 'psql -f /tmp/x test' hangs waiting
for input from stdin.  Why would we want STDIN to read from the terminal
if all commands are being read from a file with -f?

Reading the second URL, I see:

> Peter Eisentraut declared that from that point on, stdin would be
> whatever stream the \copy command came from.  I'd like to propose a
> variant on the "FROM" clause which makes good on Peter's declaration
> without breaking anything already using FROM STDIN and expecting it
> to really read from stdin.  (I think this is for the better because
> there are lots of good uses for "psql -f foo.sql < foo.dat".)

I agree with Peter stdin should be where ever the commands are coming
from.  I don't see any value to keeping backward compatibility for such
strange behavior, and adding another flag to give the reasonable
behavior seems wrong too.

I propose we just fix this and document it in the release notes.  Heck,
COPY and \copy should behave the same in determining STDIN, and right
now they don't.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Feb 2004 09:26:28 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Current-stream read for psql's \copy

What do people want to do with the current \copy behavior for stdin? 
Right now if you supply a file name with queries using psql -f, the copy
input is read from the terminal, not from the file.

I propose changing that so psql reads \copy input from the file so it is
consistent with COPY and is more predicable.  This does eliminate use of
split input where you supply the COPY input from a program, but you
could easily do:

	(echo "\copy test ..."; program) | psql test

or something like that.  We would document this change in the release
notes.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Mark Feit wrote:
> > 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.
> 
> Actually, I am confused by our current \copy behavior.  Given the
> following file:
> 
> 	CREATE TABLE test(x INT);
> 	\copy test FROM STDIN
> 	444
> 	\.
> 	SELECT * FROM test;
> 
> 'psql test </tmp/x' works fine, but 'psql -f /tmp/x test' hangs waiting
> for input from stdin.  Why would we want STDIN to read from the terminal
> if all commands are being read from a file with -f?
> 
> Reading the second URL, I see:
> 
> > Peter Eisentraut declared that from that point on, stdin would be
> > whatever stream the \copy command came from.  I'd like to propose a
> > variant on the "FROM" clause which makes good on Peter's declaration
> > without breaking anything already using FROM STDIN and expecting it
> > to really read from stdin.  (I think this is for the better because
> > there are lots of good uses for "psql -f foo.sql < foo.dat".)
> 
> I agree with Peter stdin should be where ever the commands are coming
> from.  I don't see any value to keeping backward compatibility for such
> strange behavior, and adding another flag to give the reasonable
> behavior seems wrong too.
> 
> I propose we just fix this and document it in the release notes.  Heck,
> COPY and \copy should behave the same in determining STDIN, and right
> now they don't.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Feb 2004 12:33:10 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Current-stream read for psql's \copy
On Tue, Feb 10, 2004 at 12:33:10PM -0500, Bruce Momjian wrote:
> 
> What do people want to do with the current \copy behavior for stdin? 

I'd like to hear more discussion of Tom's argument about security
before the decision is taken.

-- 
Andrew Sullivan  

Tue, 10 Feb 2004 13:57:14 -0500
Andrew Sullivan <ajs@crankycanuck.ca>
Re: [PATCHES] Current-stream read for psql's \copy
Bruce Momjian wrote:
> 
> What do people want to do with the current \copy behavior for stdin? 
> Right now if you supply a file name with queries using psql -f, the copy
> input is read from the terminal, not from the file.

Actually, I was wrong.  Right now \copy reads from psql's stdin, not
always the terminal.  It doesn't read from the same descriptor it gets
its SQL commands, unless they are the same as psql's stdin, like:

	psql test < commands.sql

You could make STDIN be the command stream, and add 'psqlstdin' for
psql's stdin, but it seems like a very little used feature.  It doesn't
seem worth documenting it, let alone adding code to allow it.

I assume \copy is designed primarily to allow reading from _local_ files
rather than only files that exist on the database server, as COPY
requires.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Feb 2004 16:35:20 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Current-stream read for psql's \copy
Andrew Sullivan wrote:
> On Tue, Feb 10, 2004 at 12:33:10PM -0500, Bruce Momjian wrote:
> > 
> > What do people want to do with the current \copy behavior for stdin? 
> 
> I'd like to hear more discussion of Tom's argument about security
> before the decision is taken.

Tom's point is that you can feed SQL to psql via -f and have \copy input
come from psql's stdin, and you can't insert SQL into psql's stdin:

	prog | psql -f commands.sql test

If commands.sql contains \copy, you can only specify copy data in
'prog', not actual SQL commands.  If you want to mix SQL commands and
\copy data in the same file, you have to use:

	psql test < commands.sql

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Feb 2004 16:41:03 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Current-stream read for psql's \copy
Bruce Momjian wrote:
> Bruce Momjian wrote:
> > 
> > What do people want to do with the current \copy behavior for stdin? 
> > Right now if you supply a file name with queries using psql -f, the copy
> > input is read from the terminal, not from the file.
> 
> Actually, I was wrong.  Right now \copy reads from psql's stdin, not
> always the terminal.  It doesn't read from the same descriptor it gets
> its SQL commands, unless they are the same as psql's stdin, like:
> 
> 	psql test < commands.sql
> 
> You could make STDIN be the command stream, and add 'psqlstdin' for
> psql's stdin, but it seems like a very little used feature.  It doesn't
> seem worth documenting it, let alone adding code to allow it.
> 
> I assume \copy is designed primarily to allow reading from _local_ files
> rather than only files that exist on the database server, as COPY
> requires.

Now, I am really confused.  First I see the \copy from '-' is already in
CVS.   I missed that commit message, but it has been in for a few weeks.

Second, I think I now see the designer's goal of using stdin/stdout for
\copy.  \copy is for reading local files rather than only server files
via COPY, but for stdin/stdout, there isn't any 'local' file that makes
it different than COPY, so I am not sure even why someone would use
\copy when they could use COPY.

Also, I came upon this gem:

	$ echo '\\copy test to stdout' | psql -o /tmp/z test
	444
	444
	444
	444
	444

Seems 'copy to stdout' also has this split idea of sending \copy output
to a different place from other output.

I guess my big question now is why someone would use \copy stdin/stdout
for reading input from the command stream when they can use COPY?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 10 Feb 2004 17:08:34 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Current-stream read for psql's \copy
Bruce Momjian wrote:
> Also, I came upon this gem:
> 
> 	$ echo '\\copy test to stdout' | psql -o /tmp/z test
> 	444
> 	444
> 	444
> 	444
> 	444
> 
> Seems 'copy to stdout' also has this split idea of sending \copy output
> to a different place from other output.
> 
> I guess my big question now is why someone would use \copy stdin/stdout
> for reading input from the command stream when they can use COPY?

I think the idea was that you could have a program that does something 
like (Perl):

my $fh = open('| psql -f commands.sql');
print $fh "1\ta\n";

Where commands.sql contains the \copy.

I'm not saying that was the original intention, but someone pointed out 
you could do things that way, and I can see it might (rarely) be useful.

-- 
   Richard Huxton
   Archonet Ltd

Tue, 18 May 2004 10:09:12 +0100
Richard Huxton <dev@archonet.com>
Re: [PATCHES] Current-stream read for psql's \copy