On Tue, Jun 06, 2006 at 04:47:40PM +0200, Harald Fuchs wrote: > In article <[EMAIL PROTECTED]>, > Andrew Dunstan <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
