postgresql 单个选择列表中的 SQL 多个 UNNEST
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23003601/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL multiple UNNEST in single select list
提问by user1192878
I was implementing a Query system. I implemented unnest function. Now user was asking about using multiple unnest in a single select statement. I was using PostgreSQL as kind of guideline since most users was using it before our query system.
我正在实施一个查询系统。我实现了 unnest 功能。现在用户询问在单个 select 语句中使用多个 unnest。我使用 PostgreSQL 作为一种指南，因为大多数用户在我们的查询系统之前使用它。
PostgreSQL has such strange behavior:
PostgreSQL 有这样奇怪的行为：
postgres=# select unnest(array[1,2]), unnest(array[1,2]);
unnest | unnest
--------+--------
1 | 1
2 | 2
(2 rows)
postgres=# select unnest(array[1,2]), unnest(array[1,2,3]);
unnest | unnest
--------+--------
1 | 1
2 | 2
1 | 3
2 | 1
1 | 2
2 | 3
(6 rows)
My implementation was always generate as Cartesian product. I'm wondering, what's the correct logic behind this? Is PostgreSQL doing right thing or just a bug? I didn't find clear description in ANSI document or PostgreSQL document.
我的实现总是作为笛卡尔积生成。我想知道，这背后的正确逻辑是什么？PostgreSQL 是在做正确的事情还是只是一个错误？我在 ANSI 文档或 PostgreSQL 文档中没有找到明确的描述。
回答by Craig Ringer
This isn't about unnest as such, but about PostgreSQL's very weird handling of multiple set-returning functions in the SELECTlist. Set-returning functions in SELECTaren't part of the ANSI SQL standard.
这不是关于 unnest 本身，而是关于 PostgreSQL 对SELECT列表中多个集合返回函数的非常奇怪的处理。集合返回函数SELECT不是 ANSI SQL 标准的一部分。
You will find behaviour much saner with LATERALqueries, which should be preferred over using a a set-returning function in FROMas much as possible:
你会发现LATERAL查询的行为更加理智，应该尽可能多地使用集合返回函数FROM：
select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;
e.g.
例如
regress=> select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;
a | b
---+---
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
(6 rows)
The only time I still use multiple set-returning functions in SELECTis when I want to pair up values from functions that both return the same number of rows. The need for that will go away in 9.4, with multi-argument unnestand with support for WITH ORDINALITY.
我唯一一次仍然使用多个集合返回函数SELECT是当我想将返回相同行数的函数的值配对时。在 9.4 中，这种需求将消失，支持多参数unnest并支持WITH ORDINALITY.
