行为1：当设置的长度相同时,它会逐个匹配每个集合中的项目
postgres=# SELECT generate_series(1,3),generate_series(5,7) order by 1,2;
generate_series | generate_series
-----------------+-----------------
1 | 5
2 | 6
3 | 7
(3 rows)
行为2：当设置的长度不同时,它会“交叉连接”这些集合
postgres=# SELECT generate_series(1,2),2;
generate_series | generate_series
-----------------+-----------------
1 | 5
1 | 6
1 | 7
2 | 5
2 | 6
2 | 7
(6 rows)
我想我在这里不了解一些事情,有人可以解释一下这个被驱逐的行为吗？
编辑：另一个例子,比以前更怪异
postgres=# SELECT generate_series(1,2) x,generate_series(1,4) y order by x,y; x | y ---+--- 1 | 1 1 | 3 2 | 2 2 | 4 (4 rows)
解决方法
为较小的集添加空值.演示：
SELECT generate_series( 1,2) AS row2,generate_series(11,13) AS row3,generate_series(21,24) AS row4;
row2 | row3 | row4 -----+------+----- 1 | 11 | 21 2 | 12 | 22 null | 13 | 23 null | null | 24
dbfiddle here
If there is more than one set-returning function in the query’s select
list,the behavior is similar to what you get from putting the
functions into a singleLATERAL ROWS FROM( ... )FROM-clause item. For
each row from the underlying query,there is an output row using the
first result from each function,then an output row using the second
result,and so on. If some of the set-returning functions produce
fewer outputs than others,null values are substituted for the missing
data,so that the total number of rows emitted for one underlying row
is the same as for the set-returning function that produced the most
outputs. Thus the set-returning functions run “in lockstep” until they
are all exhausted,and then execution continues with the next
underlying row.
这结束了传统上奇怪的行为.
Postgres 9.6或更高版本
结果行的数量(有点令人惊讶！)是同一SELECT列表中所有集合的最低公倍数. (如果所有设置大小都没有公约数,则只有CROSS JOIN！)演示：
SELECT generate_series( 1,24) AS row4;
row2 | row3 | row4 -----+------+----- 1 | 11 | 21 2 | 12 | 22 1 | 13 | 23 2 | 11 | 24 1 | 12 | 21 2 | 13 | 22 1 | 11 | 23 2 | 12 | 24 1 | 13 | 21 2 | 11 | 22 1 | 12 | 23 2 | 13 | 24
dbfiddle here
记录于manual for Postgres 9.6 the chapter SQL Functions Returning Sets,以及避免它的建议：
Note: The key problem with using set-returning functions in the select
list,rather than theFROMclause,is that putting more than one
set-returning function in the same select list does not behave very
sensibly. (What you actually get if you do so is a number of output
rows equal to the least common multiple of the numbers of rows
produced by each set-returning function.) TheLATERALSyntax produces
less surprising results when calling multiple set-returning functions,
and should usually be used instead.
大胆强调我的.
单个设置返回功能正常(但在FROM列表中仍然更清晰),但现在不鼓励在同一个SELECT列表中使用多个.在我们进行LATERAL连接之前,这是一个有用的功能.现在它只是历史上的压载物.
有关：
> Parallel unnest() and sort order in PostgreSQL
> Unnest multiple arrays in parallel
> What is the difference between LATERAL and a subquery in PostgreSQL?
