Re: COPY (query) TO file

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Supporting VALUES only in INSERT would be relatively trivial BTW,
>> but the spec actually thinks it should be allowed as a <table spec>
>> in FROM ...

> How does that syntax work?

If you look at SQL92, INSERT ... VALUES is actually not a direct
production in their BNF. They define <insert statement> as

<insert statement> ::=
INSERT INTO <table name>
<insert columns and source>

<insert columns and source> ::=
[ <left paren> <insert column list> <right paren> ]
<query expression>
| DEFAULT VALUES

<insert column list> ::= <column name list>

and then one of the alternatives for <query expression> is
<table value constructor>, which is

<table value constructor> ::=
VALUES <table value constructor list>

<table value constructor list> ::=
<row value constructor> [ { <comma> <row value constructor> }... ]

(Another alternative for <query expression> is <query specification>,
which accounts for the INSERT ... SELECT syntax.) 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. (A quick look at mysql's grammar suggests that
they don't handle those cases.)

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 :-(

regards, tom lane