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.

What is unclear exactly? How to *call* these kind of functions? You can use the array constructors http://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS or, you can write their input representation in string (then use casting optionally) http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-IO — pozs, Jan 08 '15 at 09:36
I don't know how to create stored proc with these kinds of input params. — Maki, Jan 08 '15 at 09:54
That is also covered in your previous question(s) (f.ex. http://stackoverflow.com/questions/27708234/pl-pgsql-control-structures-for-lists-arrays ) -- you can use the standard compatible ` ARRAY`, or the PostgreSQL specific `[]` syntax. http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-DECLARATION — pozs, Jan 08 '15 at 09:59

Erwin Brandstetter · Accepted Answer · 2023-06-12T01:11:57.810

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:

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

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. — Simon D, Apr 18 '18 at 02:50

score 5 · Answer 2 · 2015-01-08T12:57:05.210

5

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

edited Jan 08 '15 at 12:57

answered Jan 08 '15 at 09:59

Sorry, my mistake, it is okay. Can U use FOREACH over text array? – Maki Jan 08 '15 at 13:09
Can I use FOREACH over text array in Postgres? – Maki Jan 08 '15 at 13:12
@Maki: if everything else fails, read the manual: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY – Jan 08 '15 at 13:13

Unnest multiple arrays in parallel

2 Answers2

Linked

Related