Skip site navigation (1) Skip section navigation (2)

Re: COPY (query) TO file

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, PFC <lists(at)peufeu(dot)com>, "Tino Wildenhain" <tino(at)wildenhain(dot)de>, "Mark Woodward" <pgsql(at)mohawksoft(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: COPY (query) TO file
Date: 2006-06-03 19:09:45
Message-ID: 87ac8ucayu.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> The interesting point here is that a <subquery> is defined as a
> parenthesized <query expression>, which means that you ought to be able to
> use a parenthesized VALUES list anyplace you could use a parenthesized
> SELECT. So FROM lists, IN clauses, = ANY and friends, etc all really ought
> to be able to support this.

That's actually pretty neat. I've occasionally had to write queries with the
idiom

SELECT ... 
  FROM (SELECT a,b,c UNION ALL 
        SELECT d,e,f UNION ALL
        SELECT g,h,i
       )
 WHERE ...

That's pretty awful. It would have been awfully nice to do be able to do

SELECT ... FROM (VALUES (a,b,c),(d,e,f),(g,h,i))


> The trouble with supporting it for any case other than INSERT is that
> you have to work out what the column datatypes of the construct ought
> to be.  This is the same as the equivalent problem for UNION constructs,
> but the UNION type resolution algorithm looks kinda ugly for thousands
> of inputs :-(

I always thought UNION just decided on the type based on the first branch and
then coerced all the others to that type. I always cast all the columns on the
first union branch just in case.

-- 
greg


In response to

Responses

pgsql-hackers by date

Next:From: Mike BenoitDate: 2006-06-03 19:15:31
Subject: Re: More thoughts about planner's cost estimates
Previous:From: Nicolai PetriDate: 2006-06-03 19:05:02
Subject: Re: Faster Updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group