A table out of thin air: the VALUES table function

In 2019, getting three literal rows meant UNION ALL of single-row SELECTs or building arrays and unfolding them with ARRAY JOIN. PostgreSQL has VALUES (1, 'one'), (2, 'two') as a table. I opened #5984 asking for that.

The maintainer’s reply the next day changed the shape: not new top-level syntax but a table function taking the structure as a string, like file and url: VALUES('a UInt64, s String', (1, 'one'), ...). A contributor asked why not infer types from the first row; the objection was that (1, 'one'), (256, 'two') would give UInt8 and then not fit. The proposed fallback was the common supertype across rows, as array literals do. I preferred an optional TABLE name(types...) prefix; the verdict was that it was nicer but adds special syntax, and could still be implemented later.

PR #6209 (“VALUES list”, dimarub2000) was merged on 2019-08-01, three weeks after the issue. TableFunctionValues.cpp is present in the 19.14 release tag and in none of the 19.13 tags I checked, so 19.14 is where it first shipped.

Checked on 26.4, the structure string is optional; without it columns are named c1, c2 and types are inferred as the common type:

SELECT *, toTypeName(c1), toTypeName(c2) FROM VALUES((1, 'one'), (256, 'two'));
-- c1 is UInt16 for both rows, c2 is String

The PostgreSQL form works as a subquery source (SELECT * FROM (VALUES (1, 'a'), (2, 'b')) returns two rows on 26.4), but a standalone VALUES (...) statement is still an error. For inline data from text, see the format table function and generating test data.