I have an array of integers(nodes or destinations) i.e array[2,3,4,5,6,8] that need to be visited in the given sequence.
What I want is, to get the shortest distance using pgr_dijkstra. But the pgr_dijkstra finds the shortest path for two points, therefore I need to find the distance of each pair using pgr_dijkstra and adding all distances to get the total distance.
The pairs will be like
2,3
3,4 
4,5 
5,6 
6,8.
Is there any way to define a function that takes this array and finds the shortest path using pgr_dijkstra.
Query is:
for 1st pair(2,3)
SELECT * FROM pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads',2,3, false);
for 2nd pair(3,4)
SELECT * FROM pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads'***,3,4,*** false)
for 3rd pair(4,5)
SELECT * FROM pgr_dijkstra('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads'***,4,5,*** false);
NOTE: The array size is not fixed, it can be different.
Is there any way to automate this in postgres sql may be using a loop etc?
Please let me know how to do it.
Thank you.
If you want all pairs distance then use
select * from pgr_apspJohnson ('SELECT gid as id,source, target, rcost_len AS cost FROM finalroads)
how to get the distance of sequence of nodes in pgr_dijkstra pgrouting?