CTE recursiva

cte_recursiva.sql

--Cria uma tabela de Plano de Contas
CREATE TABLE PlanoContas (
    ID INT NOT NULL,
    IDSup INT NULL,
    Descricao character varying(50)
);

--Adiciona as constraints
ALTER TABLE PlanoContas ADD CONSTRAINT PK_Plano PRIMARY KEY (ID);

ALTER TABLE PlanoContas ADD CONSTRAINT FK_Plano FOREIGN KEY (IDSup) REFERENCES PlanoContas (ID);

INSERT INTO PlanoContas VALUES (01,NULL,'Ativo');
INSERT INTO PlanoContas VALUES (02,01,'Ativo Circulante');
INSERT INTO PlanoContas VALUES (03,02,'Disponibilidades');
INSERT INTO PlanoContas VALUES (04,03,'Caixa');
INSERT INTO PlanoContas VALUES (05,04,'Caixa Geral');
INSERT INTO PlanoContas VALUES (06,03,'Contas Bancárias');
INSERT INTO PlanoContas VALUES (07,06,'Conta Corrente');
INSERT INTO PlanoContas VALUES (08,06,'Conta Poupança');

INSERT INTO PlanoContas VALUES (09,02,'Créditos');
INSERT INTO PlanoContas VALUES (10,09,'Duplicatas');
INSERT INTO PlanoContas VALUES (11,10,'Duplicatas Descontadas');
INSERT INTO PlanoContas VALUES (12,10,'Duplicatas a Receber');
INSERT INTO PlanoContas VALUES (13,06,'Conta Investimento');

WITH recursive Res (ID, IDSup, Descricao, Nivel, Caminho) As (
    SELECT
        ID,
        IDSup,
        Descricao,
        1 As Nivel,
        repeat('0'::character, (3 - ROUND(LOG(ID)::numeric,0))::int) || ID::character || '.'
    FROM PlanoContas
    WHERE IDSup IS NULL
    UNION ALL
    SELECT
        F.ID,
        F.IDSup,
        F.Descricao,
        Nivel + 1,
        P.Caminho || repeat('0'::character,(3-ROUND(LOG(F.ID::numeric),0))::int) || F.ID::character || '.'
    FROM Res As P
    INNER JOIN PlanoContas As F ON P.ID = F.IDSup
)

SELECT
    ID, '|' || repeat('–',Nivel) || Descricao As Descricao,
    Nivel, Caminho
FROM Res
ORDER BY Caminho