Вставка в массив в SELECT по индексу в Postgres
Я использую Postgres CTE для рекурсии по дереву родитель-потомок. Следующий сценарий будет рекурсивно проходить от корня к листу и добавлять в конец path (ARRAY).
WITH RECURSIVE tree AS (
// get roots
SELECT entity_id, parent_id, ARRAY[entity_id] as path
FROM entity
WHERE parent_id is null
UNION ALL
// recursive step
SELECT c.entity_id, c.parent_id, path || c.entity_id
FROM tree t
JOIN entity c ON c.parent_id = t.entity_id
)
SELECT path
FROM tree t
WHERE entity_id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL);
Вместо того, чтобы добавлять в конец path на каждом шаге, я хотел бы вставить в массив столбец index. Возможно ли это сделать в SELECT?
Гипотетическое решение
SELECT path[c.index] = c.entity_id
FROM tree t
JOIN entity c ON c.parent_id = t.entity_id
Ожидаемый результат
| entity_id | index | parent_id |
|:-----------|------------:|:----------|
| a | 3 | d |
| b | 5 | a |
| c | 1 | (none) |
| d | 2 | c |
path = [c,d,a,(none),b]
Не понимаю, откуда взялся index? Выражение path [c.index] = path [entity_id] определенно допустимо, если c.index и entity_id являются целыми числами.
Исправлено на path[c.index] = c.entity_id. Это выражение возвращает логическое значение, которое вызывает ошибку при объединении
Ответы 1
Функция реализует присвоение arr[idx]:= elem и возвращает arr. При необходимости массив автоматически расширяется для размещения нового элемента.
create or replace function array_set_element(arr text[], elem text, idx int)
returns text[] language plpgsql as $$
begin
if cardinality(arr) < idx then
arr:= arr || array_fill(null::text, array[idx- cardinality(arr)]);
end if;
arr[idx]:= elem;
return arr;
end $$;
Пример:
select array_set_element('{a, b}'::text[], 'e', 5);
array_set_element
------------------------
{a,b,NULL,NULL,e}
(1 row)
Используйте функцию в своем запросе:
WITH RECURSIVE tree AS (
SELECT entity_id, parent_id, array_set_element('{}'::text[], entity_id, index) as path
FROM entity
WHERE parent_id is null
UNION ALL
SELECT c.entity_id, c.parent_id, array_set_element(path, c.entity_id, c.index)
FROM tree t
JOIN entity c ON c.parent_id = t.entity_id
)
SELECT path
FROM tree t
WHERE entity_id NOT IN (SELECT DISTINCT parent_id FROM tree WHERE parent_id IS NOT NULL);
path
----------------
{c,d,a,NULL,b}
(1 row)
Спасибо, это сработало, как задумано! Заменен cardinality(arr) на array_length(arr,1) для версии <9.4
Пожалуйста, Редактировать свой вопрос и добавьте образец данных и ожидаемый результат на основе этих данных. Форматированный текст пожалуйста, нет скриншотов. редактировать ваш вопрос - сделайте нет почтовый индекс или дополнительную информацию в комментариях.