Build AST with recursive CTE in Postgres

185 Views Asked by At

Given following table:

create table tree
(
    id int,
    parent_id int REFERENCES tree(id),
    operator varchar,
    primary key(id)
);

insert into tree values
(1, null, 'AND'),
(2, 1, 'NOT'),
(3, 1, 'OR'),
(4, 2, 'AND'),
(5, 3, 'Y'),
(6, 3, 'Z'),
(7, 4, 'K'),
(8, 4, 'OR'),
(9, 8, 'X'),
(10, 8, 'A'),
(11, 8, 'B')
;

how could I compute final AST: AND(NOT(AND(K, OR(X, A, B))), OR(Y, Z))

I tried different approaches with recursive CTE, but my problem is that CTE neither allows aggregations in recursive part of CTE nor subqueries where CTE is used.

Latest thing I tried was this:

with RECURSIVE tree1(id, parent_id, operator, n, cc) as (
    select t.*, 0, cc.cc 
    from tree t, lateral(select count(*) as cc from tree tt where tt.parent_id = t.id) cc 
    where t.parent_id is null
    union ALL
    select t.*, n + 1, cc.cc
    FROM tree t INNER JOIN tree1 a on t.parent_id = a.id, lateral(select count(*) as cc from tree tt where tt.parent_id = t.id) cc
), tree2(id, parent_id, operator, n, cc) AS (
    select t.id, t.parent_id, t.operator, t.n, t.cc
    from tree1 t
    where t.n = (select max(n) from tree1)
    union (
        select p.id, p.parent_id, p.operator || '(' || string_agg(c.operator, ', ') || ')' as operator, p.n, p.cc
        from tree1 p, tree2 c
        where p.id = c.parent_id
        group by p.id, p.parent_id, p.operator, p.n, p.cc
        union
        select p.id, p.parent_id, p.operator, p.n, p.cc
        from tree1 p, tree2 c 
        where p.cc = 0 and p.n + 1 = c.n
    )
)
select * from tree2

but it didn't work due to limitations of CTE.

The docs says CTE is Turing complete yet I can't find way to compute desired result. Am I missing something or is my understanding of Turing completeness wrong? :)

(I have Postgres 9.6)

0

There are 0 best solutions below