Unnest multiple arrays in parallel

by

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

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:

More Related Contents:

  1. Return rows matching elements of input array in plpgsql function
  2. Loop over array dimension in plpgsql
  3. Unnest array by one level
  4. Iterating over integer[] in PL/pgSQL
  5. PostgreSQL unnest() with element number
  6. Can PostgreSQL index array columns?
  7. When to use a linked list over an array/array list?
  8. What is the ideal growth rate for a dynamically allocated array?
  9. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  10. Can PostgreSQL have a uniqueness constraint on array elements?
  11. Compare arrays for equality, ignoring order of elements
  12. Call a set-returning function with an array argument multiple times
  13. Record returned from function has columns concatenated
  14. Using jsonb_set() for updating specific jsonb array value
  15. Function to loop through and select data from multiple tables
  16. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  17. jsonb vs jsonb[] for multiple addresses for a customer
  18. Write a program that takes an array/arraylist of integer and finds longest sub array/arraylist whose entries are equal [closed]
  19. remove an index number from array
  20. Passing arrays as parameters in bash
  21. Element-wise array replication in Matlab
  22. Store query result in a variable using in PL/pgSQL
  23. How can I convert a buffer of a slice of bytes (&[u8]) to an integer?
  24. How to filter an array of objects based on values in an inner array with jq?
  25. How to split a string by new lines in Swift
  26. Return multiple columns and rows from a function PostgreSQL instead of record
  27. Maximum single-sell profit
  28. New Array from Index Range Swift
  29. Interview question: three arrays and O(N*N)
  30. Excel VBA – How to Redim a 2D array?

Leave a Comment