nodes
id code depth parent
1 B 0 {NULL}
2 B-123 1 1
3 B-123-BLAH 2 2
...
6 B-123-BLAH-1-2-3 6 5
The codes are guaranteed not to overlap, so you can use righthanded wildcards to walk upward. I have a node at depth 3 (or deeper), and I want the depth 0 node, which is easy:
select * from nodes where depth = 0 and 'B-123-BLAH-ARGLEBARGLE' like code || '?' limit 1;
Now I want to warm a cache, so I have 1000 level 3 (and 4, and 6, etc) nodes, and I want all the level 0s. My preferred
output would be descendent.code, ancestor.*
in each tuple, for all input rows.
I am not smart enough to write this SQL.