On Thursday 2005-09-22 13:16, Andrew Dunstan wrote:
> Jim C. Nasby 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;
>
> They aren't mutually exclusive, though. And once you have code in place
> for the first part, turning the direct query case into a temp_view+copy
> is arguably just a case of syntactic sugar. I do think the direct query
> should at least be parenthesized, if we go that way.
Definitely any SELECT that might occur in COPY should be a sub-select. It
should meet any syntactic restrictions on a sub-select and it should be in
parentheses (or for the liberal, implied parentheses).
Proposed:
o Allow COPY to output from views -- Pending "Allow COPY to output from views", Allow COPY to output from
subqueries.
The rationale being that all subqueries can be the create clause of a views.
> So why not do what everyone is agreed on now? Whatever happens the work
> won't be wasted.
>
> Also, as nifty as this might be, we should also be prepared for people
> to complain that it runs a lot slower than vanilla COPY, because it
> surely will.
Why would there be a material difference in speed in the case of a simple
projection?
For example
Given
CREATE TABLE foo ( col_0 ,col_1 , . , . ,col_2N)
Then
COPY (SELECT col_0 ,col_2 , . , . ,col_2N) TO file-like-target