I've a table with some ID's of a tree structured like this:

term1_id term2_id
1507       ;2
107        ;3
1          ;5
5          ;6
5          ;7
812        ;8
14         ;9
2719       ;10
9          ;11
9          ;12

This query teoretically extracts the path i need putting everything in an array:

WITH RECURSIVE cte(path) AS ( --Recursively call on path (Works for every length)
    SELECT array[r.term1_id, r.term2_id] AS path
    FROM   temp_table r
    LEFT   JOIN temp_table r0 ON r0.term1_id = r.term2_id
    WHERE  r0.term2_id IS NULL
    SELECT r.term1_id || c.path
    FROM   cte c
    JOIN   temp_table r ON r.term2_id = c.path[1]

max_len AS (
    SELECT max(array_length(path, 1)) max_len
    FROM cte

select array_to_string(fill_with_last_element(path, 15), ',' , '*') 
from cte

But the problem is that it grabs the correct path but even the "subpath"


path: {118;119;79;10935;77;12210;107;3}
subpath: {12210;107;3;3;3;3;3;3}
subpath: {10935;77;12210;107;3;3;3;3}

I can have only on father but father can have multiple sons

How can i avoid the creation of subpath?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.