I've implemented a graph of nodes in PostgreSQL (not a tree)
the structure of the table is in this format
id | node1 | node2
--------------------
1 | 1 | 2
2 | 1 | 3
3 | 4 | 1
4 | 5 | 1
5 | 1 | 6
This shows the relationships between the node 1 and the nodes it is connected to.
...is that i need a function or method to find a particular node path in sql.
I want to call a function like SELECT getGraphPath(startnode,targetnode) and this will display the path in any form(rows, or strings)
e.g. SELECT getGraphPath(1,18) gives:
[1]-->[3]-->[17]-->[18]
[1]-->[4]-->[10]-->[18]
or even rows:
Result |
--------
1
3
17
18
I'd also like to know how to traverse the graph using breadth first search and depth first search.
Something like this:
with recursive graph_cte (node1, node2, start_id)
as
(
select node1, node2, id as start_id
from graphs
where node1 = 1 -- alternatively elect the starting element using where id = xyz
union all
select nxt.node1, nxt.node2, prv.start_id
from graphs nxt
join graph_cte prv on nxt.node1 = prv.node2
)
select start_id, node1, node2
from graph_cte
order by start_id;
(requires PostgreSQL 8.4 or higher)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With