PostgreSQL unnest() con número de elemento

Postgres 9.4 o posterior

Úselo WITH ORDINALITYpara funciones de retorno de conjuntos:

Cuando una función en la FROMcláusula tiene el sufijo WITH ORDINALITY, bigintse agrega una columna a la salida que comienza en 1 y se incrementa en 1 para cada fila de la salida de la función. Esto es más útil en el caso de funciones de devolución de conjuntos como unnest().

En combinación con la LATERALcaracterística en la página 9.3+ , y de acuerdo con este hilo en pgsql-hackers , la consulta anterior ahora se puede escribir como:

SELECCIONE t.id, a.elem, a.nr
DE tbl COMO t
UNIÓN IZQUIERDA LATERAL unnest(string_to_array(t.elements, ','))
                    CON ORDINALIDAD COMO a(elem , nr ) EN verdadero;

LEFT JOIN ... ON trueconserva todas las filas de la tabla de la izquierda, incluso si la expresión de la tabla de la derecha no devuelve ninguna fila. Si eso no te preocupa, puedes usar esta forma equivalente y menos detallada con un implícito CROSS JOIN LATERAL:

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr);

O más simple si se basa en una matriz real ( arrsiendo una columna de matriz):

SELECT t.id, a.elem, a.nr
FROM   tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr);

O incluso, con una sintaxis mínima:

SELECT id, a, ordinality
FROM   tbl, unnest(arr) WITH ORDINALITY a;

aes automáticamente el alias de tabla y columna. El nombre predeterminado de la columna de ordinalidad agregada es ordinality. Pero es mejor (más seguro, más limpio) agregar alias de columna explícitos y columnas calificadas de tabla.

De esta manera se conserva el orden original de los elementos de la matriz. El manual para unnest():

Expande una matriz en un conjunto de filas. Los elementos de la matriz se leen en orden de almacenamiento.

Postgres 8.4 - 9.3

Con row_number() OVER (PARTITION BY id ORDER BY elem)usted obtiene números de acuerdo con el orden de clasificación, no el número ordinal de la posición ordinal original en la cadena.

Puedes simplemente omitir ORDER BY:

SELECT *, row_number() OVER (PARTITION by id) AS nr
FROM  (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t;

Si bien esto normalmente funciona y nunca lo he visto fallar en consultas simples, PostgreSQL no afirma nada sobre el orden de las filas sin ORDER BY. Sucede que funciona debido a un detalle de implementación.

Para garantizar números ordinales de elementos en la cadena separada por espacios en blanco :

SELECT id, arr[nr] AS elem, nr
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS nr
   FROM  (SELECT id, string_to_array(elements, ' ') AS arr FROM tbl) t
   ) sub;

O más simple si se basa en una matriz real :

SELECCIONE id, arreglo[nr] AS elemento, nr
FROM (SELECT *, generate_subscripts( arr , 1) AS nr FROM tbl) t;

Respuesta relacionada en dba.SE:

• ¿Cómo preservar el orden original de los elementos en una matriz no anidada?

Postgres 8.1 - 8.4

Ninguna de estas funciones está disponible todavía: RETURNS TABLE, generate_subscripts(), unnest(), array_length(). Pero esto funciona:

CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT $1[i], i - array_lower($1,1) + 1
 FROM   generate_series(array_lower($1,1), array_upper($1,1)) i';

Tenga en cuenta en particular que el índice de la matriz puede diferir de las posiciones ordinales de los elementos. Considere esta demostración con una función extendida :

CREATE FUNCTION f_unnest_ord_idx(anyarray, OUT val anyelement, OUT ordinality int, OUT idx int)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT $1[i], i - array_lower($1,1) + 1, i
 FROM   generate_series(array_lower($1,1), array_upper($1,1)) i';

SELECT id, arr, (rec).*
FROM  (
   SELECT *, f_unnest_ord_idx(arr) AS rec
   FROM  (
      VALUES
        (1, '{a,b,c}'::text[])  --  short for: '[1:3]={a,b,c}'
      , (2, '[5:7]={a,b,c}')
      , (3, '[-9:-7]={a,b,c}')
      ) t(id, arr)
   ) sub;

 id |       arr       | val | ordinality | idx
----+-----------------+-----+------------+-----
  1 | {a,b,c}         | a   |          1 |   1
  1 | {a,b,c}         | b   |          2 |   2
  1 | {a,b,c}         | c   |          3 |   3
  2 | [5:7]={a,b,c}   | a   |          1 |   5
  2 | [5:7]={a,b,c}   | b   |          2 |   6
  2 | [5:7]={a,b,c}   | c   |          3 |   7
  3 | [-9:-7]={a,b,c} | a   |          1 |  -9
  3 | [-9:-7]={a,b,c} | b   |          2 |  -8
  3 | [-9:-7]={a,b,c} | c   |          3 |  -7

Comparar:

• Normalice los subíndices de la matriz para que comiencen con 1

Jan 07 '2012 04:01 Erwin Brandstetter