Unnest multiple matrici in parallelo

La mia ultima domanda Passare un array a memorizzato su postgres era un po ‘oscuro. Ora, per chiarire il mio objective:

Voglio creare una procedura memorizzata Postgres che accetterà due parametri di input. Uno sarà un elenco di alcuni importi come ad esempio (100, 40.5, 76) e l’altro sarà l’elenco di alcune fatture ('01-2222-05','01-3333-04','01-4444-08') . Dopo voglio usare questi due elenchi di numeri e personaggi e fare qualcosa con loro. Ad esempio, voglio prendere ogni importo da questa matrice di numeri e assegnarlo alla fattura corrispondente.

Qualcosa del genere in Oracle sarebbe simile a questo:

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

Naturalmente, i due tipi SIMPLEARRAYTYPE e NUMBER_TABLE sono definiti in precedenza nel DB.

Amerai questa nuova funzionalità di Postgres 9.4 :

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

unnest() con la tanto attesa (almeno per me) capacità di innestare in modo pulito più array in parallelo. Il manuale:

espandere più array (possibilmente di tipi diversi) in un insieme di righe. Questo è consentito solo nella clausola FROM;

È un’implementazione speciale della nuova funzione ROWS FROM .

La tua funzione ora può essere solo:

 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; 

Chiamata:

 SELECT * FROM multi_unnest(123, '{100, 40.5, 76}'::numeric[] , '{01-2222-05,01-3333-04,01-4444-08}'::text[]); 

Naturalmente, il modulo semplice può essere sostituito con un semplice SQL (nessuna funzione aggiuntiva):

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

Nelle versioni precedenti (Postgres 9.3- ), puoi usare la forma meno elegante e meno sicura:

 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; 

Avvertenze sulla vecchia forma abbreviata: oltre ad essere non standard per avere la funzione set-return nella lista SELECT , il numero di righe restituite sarebbe il minimo comune multiplo di ciascun numero di elementi degli array (con risultati sorprendenti per numeri non uguali). Dettagli in queste risposte correlate:

  • Parallel unnest () e ordinamento in PostgreSQL
  • Esiste qualcosa come una funzione zip () in PostgreSQL che combina due array?

Questo comportamento è stato finalmente sanificato con Postgres 10 . Molteplici funzioni di set- SELECT nell’elenco SELECT producono righe in “lock-step” ora. Vedere:

  • Qual è il comportamento previsto per più funzioni di set-return nella clausola select?

Le matrici vengono dichiarate aggiungendo [] al tipo di dati di base. Li si dichiara come parametro nello stesso modo in cui si dichiarano i parametri regolari:

La seguente funzione accetta un array di numeri interi e un array di stringhe e restituirà un testo fittizio:

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

Demo di SQLFiddle: http://sqlfiddle.com/#!15/fdb8d/1