arrayspostgresqlarraylistplpgsqlset-returning-functions

Unnest multiple arrays in parallel

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

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.

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

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

Solution

  • You will love this new feature of 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:

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

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

    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)
  LANGUAGE sql AS
$func$
SELECT _some_id, u.* FROM unnest(_amounts, _invoices) u;
$func$;

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

    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:

    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 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:

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