(Mis?)Behavior of \copy with -f and \i

Greetings...

I've run into a problem with the way \copy behaves when psql is
reading its input from a file using either the -f command line option
or the \i command. (Not that it matters in this case, but this is
PostgreSQL 7.4 on i686-pc-linux-gnu, compiled with gcc.)

The following...

---8<--SNIP---
CREATE TABLE junk (
abbrev CHAR(1),
name VARCHAR(80)
);

\copy junk (abbrev, name) FROM STDIN WITH DELIMITER '|' NULL ''
F|Foo
B|Bar
Z|Baz
\.
---8<--SNIP---

...works just fine when keyed directly into psql or is redirected from
a file (i.e., psql < junk.sql). When using -f or \i, psql sits and
waits for data and an EOF on the standard input and then proceeds to
interpret the next four lines as commands.

This was discussed several years ago in this thread:

http://archives.postgresql.org/pgsql-hackers/2000-01/msg00361.php

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'd be more than happy to write and test a patch if folks think this
would be a good thing. I'm leaning toward "FROM -" as the syntax but
am open to other ideas (i.e., "FROM HERE" or "FROM INPUT").

- Mark

P.S.: I've been using Postgre(s(95)?|SQL) in its various forms for
close to a decade, and what was a stable platform for lightweight
storage has matured into something I'd pour a few million rows into
without thinking twice about it. Thanks to everyone for all the great
work!

Mark Feit wrote:
> 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'd be more than happy to write and test a patch if folks think this
> would be a good thing. I'm leaning toward "FROM -" as the syntax but
> am open to other ideas (i.e., "FROM HERE" or "FROM INPUT").

I'm not sure about the proposed syntax, but the feature sounds quite
reasonable.

Peter Eisentraut writes:
>
> [Patch to make psql's \copy read from the current input, not just
> the standard input or a file.]
>
> I'm not sure about the proposed syntax, but the feature sounds quite
> reasonable.

I have a patch written for this that uses the "-" syntax:

\copy junk (abbrev, name) FROM - WITH DELIMITER '|' NULL ''

I considered a half-dozen or so different ways to go about it, and
this one fits in without breaking anything that already exists. The
hyphen seems like the best choice to go with STDIN and STDOUT since
there are already other programs where it implies the default input
stream and it's not something likely to be found alone as a filename.
If you find something you like better, changing it to something else
is a matter of tweaking a string in copy.c.

The patch will be posted to pgsql-patches as soon as I've had a chance
to torture it some more.

- Mark
--
"Debugging is twice as hard as writing the code in the first
place. Therefore, if you write the code as cleverly as possible, you
are, by definition, not smart enough to debug it." -- Brian Kernighan