Re: COPY (query) TO file

On Tue, Jun 06, 2006 at 04:47:40PM +0200, Harald Fuchs wrote:
> In article <44804377(dot)8070706(at)dunslane(dot)net>,
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
> > Mark Woodward wrote:
> >> Tom had posted a question about file compression with copy. I thought
> >> about it, and I want to through this out and see if anyone things it is a
> >> good idea.
> >>
> >> Currently, the COPY command only copies a table, what if it could operate
> >> with a query, as:
> >>
> >> COPY (select * from mytable where foo='bar') as BAR TO stdout
> >>
> >>
>
> > Isn't this already being worked on? The TODO list says:
>
> > Allow COPY to output from views
>
> IIRC Karel Zak posted a patch for that.
>
> > Another idea would be to allow actual SELECT statements in a COPY.
>
> > Personally I strongly favor the second option as being more flexible
> > than the first.
>
> How so? I see that
>
> psql -h somehost somedb -c "copy 'somequery' to stdout" >localfile
>
> would be more terse than
>
> psql -h somehost somedb -c "create temp view tmp as somequery; copy tmp to stdout" >localfile
>
> but what's more flexible there?

Flexibility aside, doing this via a temporary view is a very
non-intuitive way to go about it. AFAIK CREATE TEMP VIEW is also regular
DDL, which means more overhead in the system catalogs, along with more
need to vacuum.

I really fail to see why we shouldn't support copying from a query
unless there's some serious technical challenge. If there was a serious
technical challange that using a temporary view solved, we should do the
work of creating the temporary view for the user.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461