A new feature making possible to specify multiple functions with TABLE inside a FROM clause has made its way in Postgres. The same commit adds support for unnest with multiple arguments as well.
commit 784e762e886e6f72f548da86a27cd2ead87dbd1c
Author: Tom Lane
Date: Thu Nov 21 19:37:02 2013 -0500
Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.
This patch adds the ability to write TABLE( function1(), function2(), ...)
as a single FROM-clause entry. The result is the concatenation of the
first row from each function, followed by the second row from each
function, etc; with NULLs inserted if any function produces fewer rows than
others. This is believed to be a much more useful behavior than what
Postgres currently does with multiple SRFs in a SELECT list.
This syntax also provides a reasonable way to combine use of column
definition lists with WITH ORDINALITY: put the column definition list
inside TABLE(), where it's clear that it doesn't control the ordinality
column as well.
Also implement SQL-compliant multiple-argument UNNEST(), by turning
UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)).
The SQL standard specifies TABLE() with only a single function, not
multiple functions, and it seems to require an implicit UNNEST() which is
not what this patch does. There may be something wrong with that reading
of the spec, though, because if it's right then the spec's TABLE() is just
a pointless alternative spelling of UNNEST(). After further review of
that, we might choose to adopt a different syntax for what this patch does,
but in any case this functionality seems clearly worthwhile.
Andrew Gierth, reviewed by Zolt?n B?sz?rm?nyi and Heikki Linnakangas, and
significantly revised by me.
Note that as written in this commit explanation, the SQL standard specifies that it is possible to use TABLE() with only one function, this has been extended with multiple functions, and makes the behavior of multi-argument unnest the same as a TABLE clause with multiple single unnest calls. Hence there is a risk not to find the same behavior for other database products.
Edit of 2013/12/11: Following commit 53685d7, TABLE() has been renamed to ROWS FROM().
Before 9.4, TABLE was used only as a synonym for “SELECT * FROM foo” to select all the rows of a table.
=# CREATE TABLE aa AS SELECT generate_series(1,5);
SELECT 5
=# TABLE aa;
generate_series
-----------------
1
2
3
4
5
(5 rows)
From 9.4 and above versions it is possible to use TABLE in a FROM clause to group multiple functions calls. When a function returns multiple columns, the result is reorganized respecting the order of the column results returned by each function. The first columns of each function are used at first, then the second columns, etc. Here are some examples using this function that returns two integer columns with data generated by generate_series.
=# CREATE FUNCTION data(IN int, OUT int, OUT int)
-# RETURNS setof record AS $$
$# SELECT generate_series(1, $1), generate_series(1, $1 * 2);
$# $$ LANGUAGE SQL;
CREATE FUNCTION
=# SELECT * FROM data(2);
column1 | column2
---------+---------
1 | 1
2 | 2
1 | 3
2 | 4
(4 rows)
The rows returned are completed by null values if a given function returns fewer columns than others inside the same TABLE clause.
=# SELECT * FROM TABLE (data(1), data(2));
column1 | column2 | column1 | column2
---------+---------+---------+---------
1 | 1 | 1 | 1
1 | 2 | 2 | 2
N | N | 1 | 3
N | N | 2 | 4
(4 rows)
It is of course possible to specify an AS alias after TABLE:
=# SELECT * FROM TABLE (data(1), data(2)) AS z(a, b, c, d);
a | b | c | d
---+---+---+---
1 | 1 | 1 | 1
1 | 2 | 2 | 2
N | N | 1 | 3
N | N | 2 | 4 (4 rows)
If there are not enough aliases, the column names are completed by the default values, being the column names provided by the functions.
A single TABLE clause behaves like a table as a whole… To be more explicit you can perform more complex operations between TABLE clauses like joins or other things… Have a look at that.
=# SELECT * FROM TABLE (data(1)) AS a(a, b)
-# JOIN TABLE(data(1)) AS c(c, d) ON c.c = a.b;
a | b | c | d
---+---+---+---
1 | 1 | 1 | 1
1 | 1 | 1 | 2
(2 rows)
=# SELECT sum(c.d) FROM TABLE (data(1)) AS a(a, b)
-# JOIN TABLE(data(1)) AS c(c, d) ON c.c = a.b GROUP BY a.a;
sum
-----
3
(1 row)
Yes, this can become quite powerful… But wait! Of course FROM TABLE(data(arg)) is equivalent to data(arg) in my example but now imagine those things combined with multiple functions inside a single TABLE clause!
Finally, a couple or words about a direct application of TABLE for unnest(), the single-argument version of unnest() is already well-known as it expands an array into a set of rows like that:
=# select unnest(array[1,2,5]);
unnest
--------
1
2
5
(3 rows)
The multi-argument unnest is made such as it respects the same behavior than TABLE using multiple unnest calls, each of those unnest calls having one argument. So unnest(a, b, c) is equivalent to TABLE(unnest(a), unnest(b), unnest(c)). Null values are used as well when an array contains less values than others inside the same TABLE clause. Here is a short example:
=# SELECT * FROM unnest(array[1,2],array[3,4,5]);
unnest | unnest
--------+--------
1 | 3
2 | 4
N | 5
(3 rows)
unnest with multiple arguments cannot be used inside a SELECT clause, it has to be used in a FROM clause.
=# SELECT unnest(array[1,2],array[3,4,5]);
ERROR: 42883: function unnest(integer[], integer[]) does not exist
LINE 1: SELECT unnest(array[1,2],array[3,4,5]);
Et voila about that.
Note: for this post, null values are printed as ‘N’ using this psql setting:
\pset null 'N'