并行UNNEST多个阵列 [英] Unnest multiple arrays in parallel

问题描述

我的最后一个问题<一href=\"http://stackoverflow.com/questions/27823248/passing-an-array-to-stored-to-postgres/27825270#27825270\">Passing数组存储到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多个阵列并行万众期待的（至少我所）功能的干净的。 每文档：

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

拓展多个阵列（可能是不同的类型），以一组行。这是只允许在FROM子句中;

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

这是一个特殊的执行新的<一href=\"http://www.postgresql.org/docs/current/interactive/queries-table-ex$p$pssions.html#QUERIES-TABLEFUNCTIONS\"相对=nofollow> 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 （无附加功能）：

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 列表功能，甚至UNNEST的并行而不是形成了笛卡尔积，这会失败（并默认笛卡尔积）与不同数量元素的数组。在这些相关答案详细介绍：

Caveats of the old shorthand form: besides being totally non-standard to have set-returning function in the SELECT list and even unnest in parallel instead of forming a Cartesian product, this would fail (and default to a Cartesian product) for arrays with an unequal number of elements. Details in these related answers:

• Parallel PostgreSQL中
UNNEST（）和排序顺序

• Is有什么样PostgreSQL中的zip（）函数，结合两个数组？

• Parallel unnest() and sort order in PostgreSQL
• Is there something like a zip() function in PostgreSQL that combines two arrays?

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