arrays - Unnest múltiples arreglos en paralelo

postgresql arraylist

Question

Mi última pregunta Pasar una matriz a almacenada en postgres fue un poco confusa. Ahora, para aclarar mi objetivo:

Quiero crear un procedimiento almacenado de Postgres que acepte dos parámetros de entrada. Uno será una lista de algunas cantidades como por ejemplo (100, 40.5, 76) y el otro 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 este conjunto 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 definen anteriormente en DB.

Answer 1

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

La siguiente función acepta una matriz de enteros y una serie de cadenas y devolverá algo de 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 de SQLFiddle: http://sqlfiddle.com/#!15/fdb8d/1

Answer 2

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

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

unnest() con la capacidad muy esperada (al menos para mí) de descomponer varias matrices en paralelo de forma limpia . 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 función ROWS FROM .

Su función ahora solo 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 se puede reemplazar 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 abreviada: además de no ser estándar para tener una función de retorno de conjunto en la lista SELECT , el número de filas devueltas sería el múltiplo común más bajo de cada número de elementos de la matriz (con resultados sorprendentes para números desiguales). Detalles en estas respuestas relacionadas:

• Paralelo unnest () y orden de clasificación en PostgreSQL
• ¿Hay algo como una función zip () en PostgreSQL que combina dos matrices?

Este comportamiento finalmente se ha desinfectado con Postgres 10 . Múltiples funciones de retorno de conjuntos en la lista SELECT producen filas en "paso de bloqueo" ahora. Ver:

• ¿Cuál es el comportamiento esperado para múltiples funciones de retorno de conjuntos en la cláusula select?