postgresql unnest () функция и номер элемента

]

вопрос

Когда встречается столбец, содержащий разделенные значения, функция unnest () предоставляет способ извлечь эти значения:

]

myTable
id | elements
--- ------------
1  |ab,cd,efg,hi
2  |jk,lm,no,pq
3  |rstuv,wxyz

select id, unnest(string_to_array(elements, ',')) AS elem
from myTable

id | elem
--- -----
1  | ab
1  | cd
1  | efg
1  | hi
2  | jk
...

, однако, вы можете также включить номера элементов в формате следующим образом:

id | elem | nr
--- ------ ---
1  | ab   | 1
1  | cd   | 2
1  | efg  | 3
1  | hi   | 4
2  | jk   | 1
...

row_number ()

или rank () ), потому что эти функции всегда возвращаются 1, что, вероятно, связано с тем, что все элементы находятся в одном строке в таблице исходных. ] Решение

postgresql 14 или позже

для сочинения с запятой, используйте

string_to_table ()

вместо unnest (string_to_array ()) : ]

SELECT t.id, a.elem, a.nr
FROM   tbl t
LEFT   JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true

postgresql 9.4 или позже ]

для функций, которые возвращаются, используйте

с Ordinality

:

]

]

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 ... на True сохраняет все строки, можно использовать версию чистого запроса:

]

для фактических массивов (

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

для простоты можно использовать имя столбца по умолчанию:

]

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

]

также может быть дополнительно упрощено:

]

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

Эта окончательная форма возвращает все столбцы

tbl

SELECT * FROM tbl, unnest(arr) WITH ORDINALITY a

a

используется как псевдоним таблицы, так и псевдоним столбца (для первого столбца), а имя по умолчанию прикрепленной последовательности - ordinality .

]

postgresql 8.4 - 9.3 ] используйте row_number () uver (раздел по идентификационному порядку от elem) Получить номер в соответствии с порядком сортировки (не номером позиции):

]

select *, row_number () uver (раздел по id) как nr ОТ (SELECT ID, regexp_split_to_table (elements, ',') как elem from tbl) t

] , хотя это обычно действителен, и никакие сбои не наблюдаются в простых запросах, PostgreSQL не гарантирует порядок строк без

заказа по

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

разделяет номер последовательности

] в string string :

:

]]] select id, arr [nr] как elem, nr ОТ ( Выберите *, Generate_subscripts (arr, 1) как nr ИЗ (SELECT ID, string_to_array (элементы, '') как arr от tbl) t ) sub для фактического массива

можно использовать более простую версию:

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

] postgresql 8.1 - 8.4 ]

потому что версии postgresql с 8.1 до 8.4 отсутствуют определенные функции, такие как

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

,

unnest ()

и

array_length () ], вы можете использовать функцию custment scl confact scl confact sk f_unnest_ord : создать функцию f_unnest_ord (anyarray, out val anyelement, out ordinality integer) Возвращает SETOF Record Язык SQL неизменен как 'Выберите $ 1 [i], i - array_lower ($ 1,1) 1 От Generate_series (array_lower ($ 1,1), array_upper ($ 1,1)) i ' ] модифицированная функция следующая: ]

создать функцию f_unnest_ord_idx (anyarray, out val anyelement, out ordinality int, out idx int) Возвращает SETOF Record Язык SQL неизменен как 'Выберите $ 1 [i], i - array_lower ($ 1,1) 1, i От Generate_series (array_lower ($ 1,1), array_upper ($ 1,1)) i '

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'

f_unnest_ord_idx

возвращает прикрепленный

idx

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).* ОТ ( Выберите *, f_unnest_ord_idx (arr) как rec ОТ ( ЦЕННОСТИ (1, '{a, b, c}' :: text []) - коротко для: '[1: 3] = {a, b, c}' , (2, '[5: 7] = {a, b, c}') , (3, '[-9: -7] = {a, b, c}') ) t (id, arr) ) sub

Выход

]

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