并行取消嵌套多个数组 [英] Unnest multiple arrays in parallel

问题描述

我的最后一个问题 将数组传递给存储到 postgres 有点不清楚.现在，澄清我的目标:

My last question Passing an array to stored to postgres was a bit unclear. Now, to clarify my objective:

我想创建一个接受两个输入参数的 Postgres 存储过程.一个是一些金额的列表，例如(100, 40.5, 76)，另一个是一些发票的列表<代码>('01-2222-05','01-3333-04','01-4444-08').之后我想使用这两个数字和字符列表并用它们做一些事情.例如，我想从这个数字数组中取出每个金额并将其分配给相应的发票.

I want to create an Postgres stored procedure which will accept two input parameters. One will be a list of some amounts like 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.

Oracle 中的类似内容如下所示:

Something like that in Oracle would look like this:

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);

当然，SIMPLEARRAYTYPE 和 NUMBER_TABLE 两种类型在 DB 中是较早定义的.

Of course, the two types SIMPLEARRAYTYPE and NUMBER_TABLE are defined earlier in DB.

推荐答案

你会喜欢Postgres 9.4的这个新特性:

You will love this new feature of Postgres 9.4:

unnest(anyarray, anyarray [, ...])

unnest() 具有备受期待的(至少是我)能够干净地并行取消嵌套多个数组的能力.手册:

unnest() with the much anticipated (at least by me) capability to unnest multiple arrays in parallel cleanly. The manual:

将多个数组(可能是不同类型)扩展为一组行.这仅在 FROM 子句中允许；

expand multiple arrays (possibly of different types) to a set of rows. This is only allowed in the FROM clause;

这是新的ROWS FROM 功能.

It's a special implementation of the new ROWS FROM feature.

您的函数现在可以是:

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;

调用:

SELECT * FROM multi_unnest(123, '{100, 40.5, 76}'::numeric[] 
                        , '{01-2222-05,01-3333-04,01-4444-08}'::text[]);

当然，简单的形式可以换成普通SQL(无附加功能):

Of course, the simple form can be replaced with plain SQL (no additional function):

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);

在早期版本(Postgres 9.3-)中，您可以使用不太优雅和不太安全的形式:

In earlier versions (Postgres 9.3-), you can use the less elegant and less safe form:

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;

旧简写形式的注意事项:除了在 SELECT 列表中具有集合返回函数是非标准的之外，返回的行数将是每个数组元素数的最小公倍数(对于不相等的数字，结果令人惊讶).这些相关答案中的详细信息:

Caveats of the old shorthand form: besides being non-standard to have set-returning function in the SELECT list, 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:

• PostgreSQL 中的并行 unnest() 和排序顺序
• PostgreSQL 中是否有类似 zip() 函数的东西可以组合两个数组?

这种行为终于被 Postgres 10 清除了.SELECT 列表中的多个集合返回函数生成lock-step"中的行.现在.见:

This behavior has finally been sanitized with Postgres 10. Multiple set-returning functions in the SELECT list produce rows in "lock-step" now. See:

• SELECT 子句中多个返回集合的函数的预期行为是什么?

这篇关于并行取消嵌套多个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！