Re: R: feature proposal ...

AgentM wrote:
> >
> > While I'm all for COPY from views, I think I'd rather have the
> > syntactic
> > warts than code warts. ISTM that
> >
> > CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
> > COPY some_name TO stdout;
> >
> > is much uglier than
> >
> > COPY SELECT * FROM table WHERE ... TO stdout;
>
> Or, you could just allow subqueries in COPY to disambiguate the syntax:
>
> COPY (SELECT * FROM table WHERE i=1) TO stdout;

This is one area where I think Informix did a better job than us, though
we inherited COPY so I don't think we can fault the community.

In Informix, LOAD is linked to INSERT, and UNLOAD to SELECT, so you do:

LOAD FROM '/datafile' [optional flags]
INSERT INTO tab [optional columns]

and UNLOAD is:

UNLOAD TO '/datafile' [optional flags]
SELECT * FROM tab

where the SELECT can use a column list, where clause, joins, etc.

We could adopt something similar with COPY

COPY FROM '/datafile' [optional flags]
INSERT INTO tab [optional columns]

COPY TO '/datafile' [optional flags]
SELECT * FROM tab

and internally use the non-executor COPY code for a simple
INSERT/SELECT, and use the view/executor for more complex cases.

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