Current-stream read for psql's \copy

Started by Nonameabout 22 years ago26 messages

mfeit+postgresql@notonthe.net

about 22 years ago

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

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Noname (#1)

Re: 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

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Noname (#1)

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

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#3)

Re: [PATCHES] 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.

[ Moved to hackers list.]

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 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

Import Notes

Reply to msg id not found:  | Resolved by subject fallback

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#4)

Re: [PATCHES] Current-stream read for psql's \copy

Bruce Momjian <pgman@candle.pha.pa.us> writes:

'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?

Actually, that behavior is extremely useful. Consider
program-that-generates-data | psql -f somescript
The script can issue a COPY FROM STDIN to insert the data coming through
the pipe.

I propose we just fix this and document it in the release notes.

I propose we not break existing applications. If we change it the way
you suggest, there'll be no way to do the above.

regards, tom lane

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Tom Lane (#5)

Re: [PATCHES] Current-stream read for psql's \copy

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

'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?

Actually, that behavior is extremely useful. Consider
program-that-generates-data | psql -f somescript
The script can issue a COPY FROM STDIN to insert the data coming through
the pipe.

I propose we just fix this and document it in the release notes.

I propose we not break existing applications. If we change it the way
you suggest, there'll be no way to do the above.

But does anyone use such a capability? The program has to generate \.
to terminate the input, so why not have it issue the \copy too? Seems
cleaner.

-- 
  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

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#6)

Re: [PATCHES] Current-stream read for psql's \copy

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

I propose we not break existing applications. If we change it the way
you suggest, there'll be no way to do the above.

But does anyone use such a capability?

What, you're going to remove a feature because you don't think it's
used? We've been burnt by that approach before.

The program has to generate \. to terminate the input

No it doesn't. EOF will do fine. The source program doesn't
necessarily have to know anything about COPY, as long as its output is
in a format COPY can cope with (eg, tab-delimited).

regards, tom lane

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Tom Lane (#7)

Re: [PATCHES] Current-stream read for psql's \copy

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

I propose we not break existing applications. If we change it the way
you suggest, there'll be no way to do the above.

But does anyone use such a capability?

What, you're going to remove a feature because you don't think it's
used? We've been burnt by that approach before.

Yes. We get burnt by it, but we also remove stuff we don't get burnt
by, so it is a value judgement.

The program has to generate \. to terminate the input

No it doesn't. EOF will do fine. The source program doesn't
necessarily have to know anything about COPY, as long as its output is
in a format COPY can cope with (eg, tab-delimited).

The current behavior seems quite strange and counter-intuitive. We
might break the code for 1-2 people, but we will make it more
predicable for everyone using it.

-- 
  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

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#8)

Re: [PATCHES] Current-stream read for psql's \copy

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

No it doesn't. EOF will do fine. The source program doesn't
necessarily have to know anything about COPY, as long as its output is
in a format COPY can cope with (eg, tab-delimited).

The current behavior seems quite strange and counter-intuitive. We
might break the code for 1-2 people, but we will make it more
predicable for everyone using it.

I don't think it's acceptable to simply remove the functionality.
If you wanted to argue about swapping the meanings of STDIN and -
(as defined by the patch) then we could debate about which way is
more consistent and whether it's worth breaking backwards compatibility
to improve consistency. I could probably be talked into supporting
that; as you say, we've done that before. But taking out a useful
behavior that has been there a long time, simply because you've decided
it's unintuitive, is not okay.

regards, tom lane

#10

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Tom Lane (#9)

Re: [PATCHES] Current-stream read for psql's \copy

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

No it doesn't. EOF will do fine. The source program doesn't
necessarily have to know anything about COPY, as long as its output is
in a format COPY can cope with (eg, tab-delimited).

The current behavior seems quite strange and counter-intuitive. We
might break the code for 1-2 people, but we will make it more
predicable for everyone using it.

I don't think it's acceptable to simply remove the functionality.
If you wanted to argue about swapping the meanings of STDIN and -
(as defined by the patch) then we could debate about which way is
more consistent and whether it's worth breaking backwards compatibility
to improve consistency. I could probably be talked into supporting
that; as you say, we've done that before. But taking out a useful
behavior that has been there a long time, simply because you've decided
it's unintuitive, is not okay.

I will do it by vote, not because _I_ decide it is unintuitive. And I
don't have to talk _you_ into it, just a majority of developers.

-- 
  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

#11

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#3)

Re: [PATCHES] 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

#12

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#10)

Re: [PATCHES] Current-stream read for psql's \copy

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I will do it by vote, not because _I_ decide it is unintuitive. And I
don't have to talk _you_ into it, just a majority of developers.

[shrug...] Put it to a vote if you want; I feel sure you will lose.

There is another argument in favor of being able to read COPY data from
stdin (ie, not from the command script), which is that it is a security
feature that can help prevent SQL-injection attacks. In the example of
data-source-program | psql -f script
the upstream program *cannot* insert any SQL commands, it can only
source data that will go into exactly the table the script specifies.
The workaround you proposed of having the upstream issue COPY for itself
is insecure; it's quite analogous to allowing a user to enter unquoted
data into a SQL command string.

regards, tom lane

#13

scott.marlowe

scott.marlowe@ihs.com

almost 22 years ago

In reply to: Tom Lane (#9)

Re: [PATCHES] Current-stream read for psql's \copy

On Tue, 10 Feb 2004, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

No it doesn't. EOF will do fine. The source program doesn't
necessarily have to know anything about COPY, as long as its output is
in a format COPY can cope with (eg, tab-delimited).

The current behavior seems quite strange and counter-intuitive. We
might break the code for 1-2 people, but we will make it more
predicable for everyone using it.

I don't think it's acceptable to simply remove the functionality.
If you wanted to argue about swapping the meanings of STDIN and -
(as defined by the patch) then we could debate about which way is
more consistent and whether it's worth breaking backwards compatibility
to improve consistency. I could probably be talked into supporting
that; as you say, we've done that before. But taking out a useful
behavior that has been there a long time, simply because you've decided
it's unintuitive, is not okay.

Why not make a -i switch (for input file) that does it the way Bruce wants
and leave -f alone so people who depend on it behaving the way it does
won't get a surprise in their next upgrade?

#14

Mark Feit

mfeit@notonthe.net

almost 22 years ago

In reply to: Bruce Momjian (#10)

Re: [PATCHES] Current-stream read for psql's \copy

Bruce Momjian writes:

I will do it by vote, not because _I_ decide it is unintuitive. And I
don't have to talk _you_ into it, just a majority of developers.

Well, here's my vote on the subject: I purposefully avoided changing
the existing behavior because (a) it would break something I will
still find useful after the patch (b) I really think STDIN should mean
STDIN just as /some/file means /some/file.

What precipitated the patch in the first place was a need to be able
to do copies in-line to populate tables with small amounts of initial
data. I like that initial data to be neat, readable and easy to
browse/modify without having to mentally filter through a lot of
INSERTs. I have an automated system that takes a bunch of directories
containing various things like table creation and strings them
together into large SQL scripts to build, clear and destroy a
database. Previously, I was having to deliver multiple files for
database builds; now I can just deliver one that does everything.

- Mark
--
"DOS computers ... are by far the most popular, with about 70 million
machines in use worldwide. Macintosh fans ... note that cockroaches
are far more numerous than humans, and that numbers alone do not
denote a higher life form." -- New York Times, November 26, 1991

#15

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Mark Feit (#14)

Re: [PATCHES] Current-stream read for psql's \copy

Mark Feit <mfeit@notonthe.net> writes:

What precipitated the patch in the first place was a need to be able
to do copies in-line to populate tables with small amounts of initial
data.

Right, that's clearly useful. The debate at hand is about the
usefulness of the other behavior.

BTW, pg_dump scripts use COPY FROM STDIN (not \copy) to achieve this
same behavior of reading COPY data from the script file. Your patch was
thus not strictly necessary, since there was already a way to do what it
did --- but I accepted it anyway, since it seems reasonable that \copy
should be able to do it too.

What Bruce is suggesting is that we remove the old \copy behavior and
make it *only* able to do what COPY FROM STDIN does. That strikes me
as loss of important functionality ...

regards, tom lane

#16

Andrew Sullivan

ajs@crankycanuck.ca

almost 22 years ago

In reply to: Bruce Momjian (#11)

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

#17

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#11)

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

#18

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Andrew Sullivan (#16)

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

#19

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#17)

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

#20

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Tom Lane (#9)

stdin/stdout mismatch for COPY and \copy

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

No it doesn't. EOF will do fine. The source program doesn't
necessarily have to know anything about COPY, as long as its output is
in a format COPY can cope with (eg, tab-delimited).

The current behavior seems quite strange and counter-intuitive. We
might break the code for 1-2 people, but we will make it more
predicable for everyone using it.

I don't think it's acceptable to simply remove the functionality.
If you wanted to argue about swapping the meanings of STDIN and -
(as defined by the patch) then we could debate about which way is
more consistent and whether it's worth breaking backwards compatibility
to improve consistency. I could probably be talked into supporting
that; as you say, we've done that before. But taking out a useful
behavior that has been there a long time, simply because you've decided
it's unintuitive, is not okay.

I am reviewing our addition of '-' in CVS for psql \copy.

Reading the manual, I think our current code is pretty confusing/contorted:

For \copy table from filename operations, psql adds the option of using
a hyphen instead of filename. This causes \copy to read rows from the
same source that issued the command, continuing until \. is read or the
stream reaches EOF. This option is useful for populating tables in-line
within a SQL script file. In contrast, \copy from stdin always reads
from psql's standard input.

...

Note: Note the difference in interpretation of stdin and stdout between
\copy and COPY. In \copy these always refer to psql's input and output
streams. In COPY, stdin comes from wherever the COPY itself came from
(for example, a script run with the -f option), while stdout refers to
the query output stream (see \o meta-command below).

"stdin is stdin except when it isn't stdin." :-)

I think the biggest problem is that stdin/stdout in COPY is different
from \copy. I propose we make stdin/stdout consistent for COPY and
\copy, where stdin always reads from command input, and stdout always
writes to command output. This does break backward compatibility of
\copy for stdin/stdout; this change would have to be mentioned in the
release notes. COPY is unaffected.

I propose we add 'pstdin', and 'pstdout' to read from psql's stdin and
stdout. I think that will greatly simplify our documentation, and
clarify the stdin/stdout usage in all cases.

-- 
  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

#21

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#20)

Re: stdin/stdout mismatch for COPY and \copy

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I think the biggest problem is that stdin/stdout in COPY is different
from \copy. I propose we make stdin/stdout consistent for COPY and
\copy, where stdin always reads from command input, and stdout always
writes to command output. This does break backward compatibility of
\copy for stdin/stdout; this change would have to be mentioned in the
release notes. COPY is unaffected.

I propose we add 'pstdin', and 'pstdout' to read from psql's stdin and
stdout. I think that will greatly simplify our documentation, and
clarify the stdin/stdout usage in all cases.

What is "command output" and how does that differ from stdout?

I see the need to distinguish command input from psql stdin, since psql
may be reading the command from a file, but I don't see where there's
a stdout difference.

"pstdin" seems a bit contrived; I'm just as happy with using "-" as
the existing patch did. I'm fine with swapping the meanings to bring
\copy into line with COPY, though.

regards, tom lane

#22

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Tom Lane (#21)

Re: stdin/stdout mismatch for COPY and \copy

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I think the biggest problem is that stdin/stdout in COPY is different
from \copy. I propose we make stdin/stdout consistent for COPY and
\copy, where stdin always reads from command input, and stdout always
writes to command output. This does break backward compatibility of
\copy for stdin/stdout; this change would have to be mentioned in the
release notes. COPY is unaffected.

I propose we add 'pstdin', and 'pstdout' to read from psql's stdin and
stdout. I think that will greatly simplify our documentation, and
clarify the stdin/stdout usage in all cases.

What is "command output" and how does that differ from stdout?

I see the need to distinguish command input from psql stdin, since psql
may be reading the command from a file, but I don't see where there's
a stdout difference.

"pstdin" seems a bit contrived; I'm just as happy with using "-" as
the existing patch did. I'm fine with swapping the meanings to bring
\copy into line with COPY, though.

pstdout can be illustrated by this:

$ sql -f /tmp/x test
DROP TABLE
CREATE TABLE
INSERT 17225 1
1
$ sql -f /tmp/x -o /tmp/y test
1

where /tmp/x is:

DROP TABLE test;
CREATE TABLE test (x int);
INSERT INTO test VALUES (1);
\copy test to stdout

In this case, stdout is going to psql's stdout, not to the command
stdout.

The reason I didn't like '-' is that it is used mostly in Unix as
synonym for stdin, and in this case it isn't a synonym --- it actually
has different behavior. Using '-' which is a synonym for stdin causes
confusion.

I thought pstdin/pstdout were very clear in helping folks remember how
it is different from stdin/stdout. Certainly it is contrived. Do you
like appstdin and appstdout better, or psqlstdin and psqlstdout?

-- 
  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

#23

Tom Lane

tgl@sss.pgh.pa.us

almost 22 years ago

In reply to: Bruce Momjian (#22)

Re: stdin/stdout mismatch for COPY and \copy

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

What is "command output" and how does that differ from stdout?

In this case, stdout is going to psql's stdout, not to the command
stdout.

Hm, I didn't realize it worked like that. So "command output" means
"where SELECT output would go"? Is that what happens to COPY TO STDOUT?
(experiments ... I guess so.)

I thought pstdin/pstdout were very clear in helping folks remember how
it is different from stdin/stdout.

Fair enough. If we need two flavors of stdout too, then I agree that
names are better than "-".

regards, tom lane

#24

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Tom Lane (#23)

Re: stdin/stdout mismatch for COPY and \copy

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

What is "command output" and how does that differ from stdout?

In this case, stdout is going to psql's stdout, not to the command
stdout.

Hm, I didn't realize it worked like that. So "command output" means
"where SELECT output would go"? Is that what happens to COPY TO STDOUT?
(experiments ... I guess so.)

I thought pstdin/pstdout were very clear in helping folks remember how
it is different from stdin/stdout.

Fair enough. If we need two flavors of stdout too, then I agree that
names are better than "-".

We could get away with using '-' for stdin and stdout because we know by
the "to"/"from" which it is, but again, the use of a synonym is
confusing.

-- 
  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

#25

Bruce Momjian

pgman@candle.pha.pa.us

almost 22 years ago

In reply to: Tom Lane (#23)

1 attachment(s)

Re: stdin/stdout mismatch for COPY and \copy

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Tom Lane wrote:

What is "command output" and how does that differ from stdout?

In this case, stdout is going to psql's stdout, not to the command
stdout.

Hm, I didn't realize it worked like that. So "command output" means
"where SELECT output would go"? Is that what happens to COPY TO STDOUT?
(experiments ... I guess so.)

I thought pstdin/pstdout were very clear in helping folks remember how
it is different from stdin/stdout.

Fair enough. If we need two flavors of stdout too, then I agree that
names are better than "-".

OK, the attached patch implements pstdin and pstdout, and change stdin
and stdout for \copy to be the same as COPY. Using this file:

drop table test;
create table test (x int);
insert into test values (1);
\copy test from stdin

I get for stdin:

$ psql -f /tmp/x -o /dev/null test

for pstdin:

$ psql -f /tmp/x -o /dev/null test
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
test> 33
test> \.

for stdout:

$ psql -f /tmp/x -o /dev/null test

for pstdout:

$ psql -f /tmp/x -o /dev/null test
1

This breaks backward compatibility and has to be mentioned in the
release notes.

-- 
  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

Attachments:

/pgpatches/copytext/plainDownload

Index: doc/src/sgml/ref/psql-ref.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.109
diff -c -c -r1.109 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml	30 Mar 2004 15:54:33 -0000	1.109
--- doc/src/sgml/ref/psql-ref.sgml	10 Apr 2004 15:18:58 -0000
***************
*** 706,712 ****
          <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>' ]
--- 706,712 ----
          <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 | pstdin | pstdout }
          [ <literal>with</literal> ] 
              [ <literal>oids</literal> ] 
              [ <literal>delimiter [as] </literal> '<replaceable class="parameter">character</replaceable>' ]
***************
*** 736,753 ****
  	</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 <replaceable
! 	class="parameter">filename</replaceable>.  This causes
! 	<literal>\copy</literal> to read rows from the same source 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 SQL script file.
! 	In contrast, <literal>\copy from stdin</> always reads from
! 	<application>psql</application>'s standard input.
  	</para>
  
          <tip>
--- 736,752 ----
  	</para>
  
  	<para>
! 	<literal>\copy <replaceable
  	class="parameter">table</replaceable> from <replaceable
! 	class="parameter">stdin | stdout</replaceable></literal>
! 	reads/writes based on the command input and output respectively.
! 	All rows are read from the same source that issued the command,
! 	continuing until <literal>\.</literal> is read or the stream
! 	reaches <acronym>EOF</>. Output is sent to the same place as
! 	command output. To read/write from
! 	<application>psql</application>'s standard input or output, use
! 	<literal>pstdin</> or <literal>pstdout</>. This option is useful
! 	for populating tables in-line within a SQL script file.
  	</para>
  
          <tip>
***************
*** 759,778 ****
          </para>
          </tip>
  
-         <note>
-         <para>
-         Note the difference in interpretation of
-         <literal>stdin</literal> and <literal>stdout</literal> between
- 	<literal>\copy</literal> and <command>COPY</command>.
- 	In <literal>\copy</literal> these always
-         refer to <application>psql</application>'s input and output
-         streams. In <command>COPY</command>, <literal>stdin</literal> comes
- 	from wherever the <command>COPY</command> itself came from (for
-         example, a script run with the <option>-f</option> option), while
-         <literal>stdout</literal> refers to the query output stream (see
-         <command>\o</command> meta-command below).
-         </para>
-         </note>
          </listitem>
        </varlistentry>
  
--- 758,763 ----
Index: src/bin/psql/copy.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/copy.c,v
retrieving revision 1.42
diff -c -c -r1.42 copy.c
*** src/bin/psql/copy.c	29 Jan 2004 12:34:59 -0000	1.42
--- src/bin/psql/copy.c	10 Apr 2004 15:19:05 -0000
***************
*** 62,68 ****
  	char	   *table;
  	char	   *column_list;
  	char	   *file;			/* NULL = stdin/stdout */
! 	bool		in_dash;		/* true = use src stream not true stdin */
  	bool		from;
  	bool		binary;
  	bool		oids;
--- 62,68 ----
  	char	   *table;
  	char	   *column_list;
  	char	   *file;			/* NULL = stdin/stdout */
! 	bool		psql_inout;		/* true = use psql stdin/stdout */
  	bool		from;
  	bool		binary;
  	bool		oids;
***************
*** 220,240 ****
  	if (strcasecmp(token, "stdin") == 0 ||
  		strcasecmp(token, "stdout") == 0)
  	{
! 		result->in_dash = false;
  		result->file = NULL;
  	}
! 	else 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;
  		result->file = pg_strdup(token);
  		expand_tilde(&result->file);
  	}
--- 220,237 ----
  	if (strcasecmp(token, "stdin") == 0 ||
  		strcasecmp(token, "stdout") == 0)
  	{
! 		result->psql_inout = false;
  		result->file = NULL;
  	}
! 	else if (strcasecmp(token, "pstdin") == 0 ||
! 		strcasecmp(token, "pstdout") == 0)
  	{
! 		result->psql_inout = true;
  		result->file = NULL;
  	}
  	else
  	{
! 		result->psql_inout = false;
  		result->file = pg_strdup(token);
  		expand_tilde(&result->file);
  	}
***************
*** 394,400 ****
  	{
  		if (options->file)
  			copystream = fopen(options->file, "r");
! 		else if (options->in_dash)
   			copystream = pset.cur_cmd_source;
  		else
   			copystream = stdin;
--- 391,397 ----
  	{
  		if (options->file)
  			copystream = fopen(options->file, "r");
! 		else if (!options->psql_inout)
   			copystream = pset.cur_cmd_source;
  		else
   			copystream = stdin;
***************
*** 403,408 ****
--- 400,407 ----
  	{
  		if (options->file)
  			copystream = fopen(options->file, "w");
+ 		else if (!options->psql_inout)
+  			copystream = pset.queryFout;
  		else
  			copystream = stdout;
  	}

#26

Richard Huxton

dev@archonet.com

over 21 years ago

In reply to: Bruce Momjian (#19)

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