clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 497295 fiddles created (9523 in the last week).

CREATE TABLE tree ( id INT PRIMARY KEY , parent_id INT , name text );
 hidden batch(es)


INSERT INTO tree VALUES (2, 12, 'Node 2'), (3, 2, 'Node 3'), (4, 6, 'Node 4'), (5, 1, 'Node 5'), (6, 1, 'Node 6'), (7, 11, 'Node 7'), (8, 6, 'Node 8'), (10, 7, 'Node 10'), (11, 6, 'Node 11'), (12, 5, 'Node 12'), (17, 14, 'Node 17');
11 rows affected
 hidden batch(es)


CREATE FUNCTION get_parent(node_id int) RETURNS integer AS $$ WITH RECURSIVE get_parent AS ( SELECT t1.id, t1.parent_id, t1.name, 0 AS level FROM tree t1 WHERE t1.id = node_id UNION ALL SELECT t2.id, t2.parent_id, t2.name, level+1 FROM tree t2 INNER JOIN get_parent ON get_parent.parent_id = t2.id ) SELECT id FROM get_parent ORDER BY level DESC LIMIT 1 ; $$ LANGUAGE SQL;
 hidden batch(es)


select get_parent(7);
get_parent
6
 hidden batch(es)


select get_parent(1);
get_parent
 hidden batch(es)


WITH RECURSIVE childs AS ( SELECT t1.id, t1.parent_id, t1.name FROM tree t1 WHERE t1.id = get_parent(7) UNION ALL SELECT t2.id, t2.parent_id, t2.name FROM tree t2 INNER JOIN childs ON childs.id = t2.parent_id ) SELECT id, parent_id, name FROM childs;
id parent_id name
6 1 Node 6
4 6 Node 4
8 6 Node 8
11 6 Node 11
7 11 Node 7
10 7 Node 10
 hidden batch(es)