clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601395 fiddles created (48031 in the last week).

CREATE TABLE nodes ( node_id int not null primary key -- , other attributes of a node );
 hidden batch(es)


CREATE TABLE parent_nodes ( node_id int not null primary key references nodes (node_id) , parent_id int not null references nodes (node_id) );
 hidden batch(es)


CREATE TABLE ancestor_nodes ( node_id int not null references nodes (node_id) , ancestor_id int not null references nodes (node_id) );
 hidden batch(es)


create trigger add_closure after insert on parent_nodes referencing new as n for each row begin insert into ancestor_nodes (node_id, ancestor_id) values (n.node_id, n.parent_id) union all select n.node_id, ancestor_id from ancestor_nodes where node_id = n.parent_id union all select node_id, n.parent_id from ancestor_nodes where ancestor_id = n.node_id union all select a.node_id, b.ancestor_id from ancestor_nodes a cross join ancestor_nodes b where a.ancestor_id = n.node_id and b.node_id = n.parent_id; -- end;
 hidden batch(es)


insert into nodes (node_id) values (1),(2),(3),(4),(5);
 hidden batch(es)


insert into parent_nodes (node_id, parent_id) values (5,4),(4,3),(3,1),(2,1);
 hidden batch(es)


select * from ancestor_nodes;
NODE_ID ANCESTOR_ID
5 4
5 3
4 3
5 1
4 1
3 1
2 1
 hidden batch(es)