Unnest multiple arrays in parallel

Question

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.

Devour

What is unclear exactly? How to call these kind of functions? You can use the array constructors postgresql.org/docs/current/static/… or, you can write their input representation in string (then use casting optionally) postgresql.org/docs/current/static/arrays.html#ARRAYS-IO – Munitions

I don't know how to create stored proc with these kinds of input params. – Devour

That is also covered in your previous question(s) (f.ex. #27708734 ) -- you can use the standard compatible <type> ARRAY, or the PostgreSQL specific <type>[] syntax. postgresql.org/docs/current/static/… – Munitions

Answer 1

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:

• 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 SELECT list produce rows in "lock-step" now. See:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

Airburst

The documentation for the "rows from" syntax in the documentation is a little confusing and there is no example. Just for reference select * from rows from(unnest('{1,2,3}'::integer[]), unnest('{4,5,6}'::integer[]), unnest('{7,8}'::integer[])) u(a, b, c); will demonstrate basic usage - the "rows from" construction returns a table that can be used in the from clause. – Greenwell

Answer 2

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

Roxannaroxanne

Sorry, my mistake, it is okay. Can U use FOREACH over text array? – Devour

Can I use FOREACH over text array in Postgres? – Devour

@Maki: if everything else fails, read the manual: postgresql.org/docs/current/static/… – Roxannaroxanne