Unnest matrices múltiples en paralelo

Mi última pregunta Pasar una matriz para almacenar en postgres era un poco confusa. Ahora, para aclarar mi objective:

Quiero crear un procedimiento almacenado de Postgres que aceptará dos parámetros de entrada. Una será una lista de algunas cantidades como, por ejemplo, (100, 40.5, 76) y la otra será una lista de algunas facturas ('01-2222-05','01-3333-04','01-4444-08') . Después de eso, quiero usar estas dos listas de números y caracteres y hacer algo con ellos. Por ejemplo, quiero tomar cada cantidad de esta matriz de números y asignarla a la factura correspondiente.

Algo así en Oracle se vería así:

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

Por supuesto, los dos tipos SIMPLEARRAYTYPE y NUMBER_TABLE se definieron anteriormente en DB.

Le encantará esta nueva característica de Postgres 9.4 :

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

unnest() con la capacidad muy esperada (al menos por mí) de deshacer matrices múltiples en paralelo limpiamente . El manual:

expandir múltiples matrices (posiblemente de diferentes tipos) a un conjunto de filas. Esto solo está permitido en la cláusula FROM;

Es una implementación especial de la nueva característica ROWS FROM .

Tu función ahora puede ser:

 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; 

Llamada:

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

Por supuesto, la forma simple puede reemplazarse con SQL simple (sin función adicional):

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

En versiones anteriores (Postgres 9.3- ), puede usar la forma menos elegante y menos segura:

 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; 

Advertencias de la antigua forma taquigráfica: además de no ser estándar para tener la función de retorno de conjunto en la lista SELECT , el número de filas devuelto sería el mínimo común múltiplo de cada número de elementos de la matriz (con resultados sorprendentes para números desiguales). Detalles en estas respuestas relacionadas:

  • Parallel unnest () y orden de clasificación en PostgreSQL
  • ¿Hay algo así como una función zip () en PostgreSQL que combine dos matrices?

Este comportamiento finalmente ha sido desinfectado con Postgres 10 . Las múltiples funciones de retorno de conjunto en la lista SELECT producen filas en “paso de locking” ahora. Ver:

  • ¿Cuál es el comportamiento esperado para las funciones de devolución de conjuntos múltiples en la cláusula de selección?

Las matrices se declaran agregando [] al tipo de datos base. Usted los declara como un parámetro de la misma manera que declara los parámetros regulares:

La siguiente función acepta una matriz de enteros y una matriz de cadenas y devolverá un texto ficticio:

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

Demostración SQLFiddle: http://sqlfiddle.com/#!15/fdb8d/1