postgresql 并行取消嵌套多个数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27836674/
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
Unnest multiple arrays in parallel
提问by Maki
My last question Passing an array to stored to postgreswas a bit unclear. Now, to clarify my objective:
我的最后一个问题将数组传递给存储到 postgres有点不清楚。现在，澄清我的目标：
I want to create an Postgres stored procedure which will accept two input parameters. One will be a list of some amountslike for instance (100, 40.5, 76)and the other one will be list of some invoices('01-2222-05','01-3333-04','01-4444-08'). After that I want to use these two lists of numbers and characters and do something with them. For example I want to take each amount from this array of numbers and assign it to corresponding invoice.
我想创建一个接受两个输入参数的 Postgres 存储过程。一个是一些金额的清单，例如(100, 40.5, 76)，另一个是一些发票的清单('01-2222-05','01-3333-04','01-4444-08')。之后我想使用这两个数字和字符列表并用它们做一些事情。例如，我想从这个数字数组中取出每个金额并将其分配给相应的发票。
Something like that in Oracle would look like this:
Oracle 中的类似内容如下所示：
SOME_PACKAGE.SOME_PROCEDURE (
789,
SYSDATE,
SIMPLEARRAYTYPE ('01-2222-05','01-3333-04','01-4444-08'),
NUMBER_TABLE (100,40.5,76),
'EUR',
1,
P_CODE,
P_MESSAGE);
Of course, the two types SIMPLEARRAYTYPEand NUMBER_TABLEare defined earlier in DB.
当然，这两种类型SIMPLEARRAYTYPE，并NUMBER_TABLE在DB早先定义。
回答by Erwin Brandstetter
You will lovethis new feature of Postgres 9.4:
你会喜欢Postgres 9.4 的这个新特性：
unnest(anyarray, anyarray [, ...])unnest()with the much anticipated (at least by me) capability to unnest multiple arrays in parallel cleanly. The manual:
unnest()具有非常期待（至少是我）能够干净地并行取消嵌套多个数组的能力。手册：
expand multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause;
将多个数组（可能是不同类型）扩展为一组行。这仅在 FROM 子句中允许；
It's a special implementation of the new ROWS FROMfeature.
这是新ROWS FROM功能的特殊实现。
Your function can now just be:
您的功能现在可以是：
CREATE OR REPLACE FUNCTION multi_unnest(_some_id int
, _amounts numeric[]
, _invoices text[])
RETURNS TABLE (some_id int, amount numeric, invoice text) AS
$func$
SELECT _some_id, u.* FROM unnest(_amounts, _invoices) u;
$func$ LANGUAGE sql;Call:
称呼：
SELECT * FROM multi_unnest(123, '{100, 40.5, 76}'::numeric[]
, '{01-2222-05,01-3333-04,01-4444-08}'::text[]);
Of course, the simple form can be replaced with plain SQL(no additional function):
当然，简单的形式也可以换成普通的SQL（无附加功能）：
SELECT 123 AS some_id, *
FROM unnest('{100, 40.5, 76}'::numeric[]
, '{01-2222-05,01-3333-04,01-4444-08}'::text[]) AS u(amount, invoice);
In earlier versions (Postgres 9.3-), you can use the less elegant and less safe form:
在早期版本（Postgres 9.3-）中，您可以使用不太优雅且不太安全的形式：
SELECT 123 AS some_id
, unnest('{100, 40.5, 76}'::numeric[]) AS amount
, unnest('{01-2222-05,01-3333-04,01-4444-08}'::text[]) AS invoice;
Caveats of the old shorthand form: besides being non-standard to have set-returning function in the SELECTlist, the number of rows returned would be the lowest common multiple of each arrays number of elements (with surprising results for unequal numbers). Details in these related answers:
旧简写形式的注意事项：除了在SELECT列表中具有集合返回函数是非标准的之外，返回的行数将是每个数组元素数的最小公倍数（对于不相等的数字会产生令人惊讶的结果）。这些相关答案中的详细信息：
- Parallel unnest() and sort order in PostgreSQL
- Is there something like a zip() function in PostgreSQL that combines two arrays?
This behavior has finally been sanitized with Postgres 10. Multiple set-returning functions in the SELECTlist produce rows in "lock-step" now. See:
Postgres 10终于消除了这种行为。SELECT列表中的多个集合返回函数现在以“锁定步骤”生成行。看：
回答by a_horse_with_no_name
Arrays are declared by adding []to the base datatype. You declare them as a parameter the same way you declare regular parameters:
数组是通过添加[]到基本数据类型来声明的。您可以像声明常规参数一样将它们声明为参数：
The following function accepts an array of integers and and array of strings and will return some dummy text:
以下函数接受整数数组和字符串数组，并将返回一些虚拟文本：
create function array_demo(p_data integer[], p_invoices text[])
returns text
as
$$
select p_data[1] || ' => ' || p_invoices[1];
$$
language sql;
select array_demo(array[1,2,3], array['one', 'two', 'three']);
SQLFiddle demo: http://sqlfiddle.com/#!15/fdb8d/1
SQLFiddle 演示：http://sqlfiddle.com/#!15/fdb8d/1
