Repeatly split multiple arrays

Question

My last question Passing an array stored in postgres is somewhat unclear. Now, to clarify my purpose:

I want to create a Postgres stored procedure that will take two input parameters. One of them will be a list of some amounts, for example, (100, 40.5, 76) , and the other will be a 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 sum from this array of numbers and assign it to the corresponding account.

Something like this 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 defined earlier in the database.

Answer 1

You will love this new Postgres 9.4 feature:

 unnest(anyarray, anyarray [, ...]) 

unnest() with the expected (at least by me) ability to freely ignore multiple arrays. Leadership:

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

This is a special implementation of the new ROWS FROM .

Your function may now be as follows:

 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, a simple form can be replaced with simple SQL (without an 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 a less elegant and less secure 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; 

Cautions of the old abbreviated form: besides the fact that it is completely non-standard for the set-return function in the SELECT list and even turned off in parallel, instead of forming a Cartesian product, this will not work (and by default the Cartesian product) for arrays with an unequal number of elements. Details in these related answers:

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

This behavior was finally sanitized by Postgres 10 . Multiple function-returning functions in the SELECT list now produce lines in the "lock-step". Cm:

• What is the expected behavior for multiple set-returning functions in a select clause?

Answer 2

Arrays are declared by appending [] to the underlying data type. You declare them as a parameter in the same way as regular parameters:

The following function takes an array of integers and an array of strings and returns 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