Рекурсивный запрос

Рейтинг: 0Ответов: 1Опубликовано: 24.03.2015

В общем, имею рекурсивный запрос:

WITH RECURSIVE temp1 ( p_id,p_parent,p_title,PATH, LEVEL, NAME  ) AS (
SELECT T1.p_id,T1.p_parent, T1.p_title, CAST (T1.p_id AS VARCHAR (50)) as PATH, 1 , CAST (T1.p_title AS VARCHAR (255)) as NAME
    FROM "SC_Wonder".t_page T1 WHERE T1.p_parent IS NULL
union
select T2.p_id, T2.p_parent, T2.p_title, CAST ( temp1.PATH ||'->'|| T2.p_id AS VARCHAR(50)) ,LEVEL + 1 ,
CAST (  '   '|| temp1.NAME AS VARCHAR(255))
     FROM "SC_Wonder".t_page T2 INNER JOIN temp1 ON( temp1.p_id= T2.p_parent))
select * from temp1 ORDER BY PATH LIMIT 100

Он выдает

в поле NAME

_10

______10

и прочее.

Причем 10 - это имя первой записи.
Как победить и заставить выводить имя текущей записи?

postgresql
Источник: Stack Overflow на русском

Ответы

▲ 1
WITH RECURSIVE temp1 ( p_id,p_parent,p_title,PATH, LEVEL, NAME  ) AS (
SELECT T1.p_id,T1.p_parent, T1.p_title, CAST (T1.p_id AS VARCHAR (50)) as PATH, 1 , 
CAST (T1.p_title AS VARCHAR (255)) as NAME
    FROM "SC_Wonder".t_page T1 WHERE T1.p_parent IS NULL
union
select T2.p_id, T2.p_parent, T2.p_title, CAST ( temp1.PATH ||'->'|| T2.p_id AS VARCHAR(50)) ,LEVEL + 1 ,
CAST (  lpad('', (LEVEL + 1)*3, ' ') || T2.p_title AS VARCHAR(255))
     FROM "SC_Wonder".t_page T2 INNER JOIN temp1 ON( temp1.p_id= T2.p_parent))
select * from temp1 ORDER BY PATH LIMIT 100

соль в 

CAST (  lpad('', (LEVEL + 1)*3, ' ') || T2.p_title AS VARCHAR(255))