Re: COPY (query) TO file

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