Re: COPY (query) TO file

Tom Lane <tgl@sss.pgh.pa.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