PostgreSQL unnest () avec le numéro d'élément

Postgres 9,4 ou plus tard

Use WITH ORDINALITY pour les fonctions de retour de jeu:

Lorsqu'une fonction de la clause FROM est suffixée par WITH ORDINALITY , a La colonne bigint est ajoutée à la sortie qui commence par 1 et incrémente de 1 pour chaque ligne de la sortie de la fonction. Ce qui est le plus utile dans le cas du retour des fonctions telles que UNNEST() .

en combinaison avec le LATERAL caractéristique dans pg 9.3+ , et selon ce fil sur pgsql-hackers , la requête ci-dessus peut maintenant être écrit comme:

SELECT t.id, a.elem, a.nr
FROM   tbl AS t
LEFT   JOIN LATERAL unnest(string_to_array(t.elements, ','))
                    WITH ORDINALITY AS a(elem, nr) ON TRUE;

LEFT JOIN ... ON TRUE conserve toutes les lignes de la table de gauche, même si l'expression table de droite ne renvoie aucune ligne. Si cela ne vous concerne pas, vous pouvez utiliser ceci autrement. équivalent, moins verbeux forme avec un implicite CROSS JOIN LATERAL :

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

ou plus simple si basé sur un tableau actuel ( arr étant une colonne tableau):

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

ou même, avec une syntaxe minimale:

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

a est automatiquement table et alias de colonne. Le nom par défaut de la colonne d'ordinalité ajoutée est ordinality . Mais il est préférable (plus sûr, plus propre) d'ajouter des alias de colonne explicites et des colonnes de qualification de table.

Postgresql 8.4. - 9.3

avec row_number() OVER (PARTITION BY id ORDER BY elem) vous obtenez des nombres selon l'ordre de tri, pas le nombre ordinal de la position initiale ordinale dans la chaîne.

vous pourriez tout simplement omettre le ORDER BY :

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

Alors que cela fonctionne normalement et j'ai jamais vu il casse dans les requêtes simples, PostgreSQL n'affirme rien concernant l'ordre des rangs sans ORDER BY . Il se trouve que cela fonctionne grâce à un détail de mise en œuvre.

à numéros ordinaux de garantie d'éléments dans le blanc-séparé chaîne :

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;

ou plus simple si basé sur un tableau actuel :

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

Liés à la réponse sur le dba.SE:

• comment préserver l'ordre d'origine des éléments dans un ensemble non testé?

Postgres 8.1 - 8.4

aucune de ces fonctionnalités ne sont disponibles, encore: RETURNS TABLE , generate_subscripts() , unnest() , array_length() .

Mais cela fonctionne:

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

noter en particulier que l'index du tableau peut différer des positions ordinales des éléments. Considérez cette démo avec une fonction étendue :

CREATE FUNCTION f_unnest_ord_idx(anyarray, OUT val anyelement, OUT ordinality int, OUT idx int)
  RETURNS SETOF record  LANGUAGE sql IMMUTABLE AS
'SELECT [i], i - array_lower(,1) + 1, i
 FROM   generate_series(array_lower(,1), array_upper(,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

comparer:

• normaliser les indices des réseaux pour les réseaux unidimensionnels de sorte qu'ils commencent par 1