Добрый день.
Возникла задача написать рекурсивный запрос (для обхода дерева) и не как не могу справиться:

WITH RECURSIVE temp1 (id, id_parent, _name, _desc, id_region, arr) AS
(
SELECT CG1.id, CG1.id_parent, CG1._name, CG1._desc, CG1.id_region, arr.* FROM counter_groups CG1,
(select array(select C1.id FROM counter C1 where C1.id_group=6)) arr
WHERE CG1.id=6

UNION

SELECT CG2.id, CG2.id_parent, CG2._name, CG2._desc, CG2.id_region, arr.* FROM temp1 tmpl,
(select array(select C2.id FROM counter C2, counter_groups CGG where C2.id_group=CGG.id and CGG.id_parent=/*6*/tmpl.id)) AS arr, counter_groups CG2
-- INNER JOIN temp1 ON (CG2.id_parent = temp1.id)
WHERE CG2.id_parent = tmpl.id
)

SELECT * FROM temp1;


Проблема в куске кода CGG.id_parent=/*6*/tmpl.id), постгрес пишет:

ОШИБКА: в элементе предложения FROM неверная ссылка на таблицу "tmpl"
LINE 30: ...G where C2.id_group=CGG.id and CGG.id_parent=/*6*/tmpl.id)) ...
^
HINT: Таблица "tmpl" присутствует в запросе, но сослаться на неё из этой части запроса нельзя.
********** Ошибка **********

ОШИБКА: в элементе предложения FROM неверная ссылка на таблицу "tmpl"
SQL-состояние: 42P01
Подсказка: Таблица "tmpl" присутствует в запросе, но сослаться на неё из этой части запроса нельзя.
Символ: 1091

Если вручную заменить tmpl.id на правильный айдишник записи, то логика работает

Возможно кто-то знает как сослаться на рекурсивную таблицу tmpl во вложенном запросе. Или может быть вообще запрос надо по другому писать?
Пожалуйста, дайте совет, кто сталкивался.

Alexius,

спасибо за подсказку, сейчас буду копать в направлении lateral

AlexiusNikyy,

чтобы ссылаться в arr на предыдущую таблицу нужно наверное lateral использовать.

Alexius,
вы правы, LATERAL дал требуемый эффект, еще раз спасибо большое.


Вот рабочая конструкция:

WITH RECURSIVE counters_list (id, id_parent, _name, _desc, id_region, arr) AS
(
SELECT cg1.id, cg1.id_parent, cg1._name, cg1._desc, cg1.id_region, arr1.* FROM counter_groups cg1,
LATERAL (select array(select cn1.id FROM counter cn1 WHERE cn1.id_group=cg1.id ORDER BY cn1.sn )) arr1
WHERE cg1.id=6

UNION

SELECT cg2.id, cg2.id_parent, cg2._name, cg2._desc, cg2.id_region, arr2.* FROM counters_list cnl1, counter_groups cg2,
LATERAL (select array(select cn2.id FROM counter cn2 WHERE cn2.id_group=cg2.id and cg2.id_parent=cnl1.id ORDER BY cn2.sn )) AS arr2
WHERE cg2.id_parent = cnl1.id
)

SELECT * FROM counters_list order by id;


Вот первые строки выполненного запроса:

69;67;"0,4 №2";П-4";;"{20054,20062,20055,20064,20060,20059,20063}"
70;60;"0,4 №2";"П-1";;"{}"
71;6;"П 18";"";;"{20068}"