Re: COPY (query) TO file

On Tue, Jun 06, 2006 at 04:47:40PM +0200, Harald Fuchs wrote:
> In article <44804377.8070706@dunslane.net>,
> Andrew Dunstan <andrew@dunslane.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461