Find shortest and longest path with recursive postgreSql

Question

I am new to PostgreSQL and in recursive queries and have this following problem: I have the following tables

• node(id, colour)
• edge(n1,n2)

I have to write a query with a recursive statement that returns the shortest and longest paths between each pair of nodes. The output should look like this: (id1,id2, shortpath,longpath)

Answer 1

SQL is probably not the best tool to do this, but you can do it using a recursive CTE.

For example:

with recursive
p as (
  select n1, n2 as n, '/' || n1 || '/' as path, 1 as cost from edge where n1 = 'A'
 union all
  select p.n1, e.n2, p.path || e.n2 || '/', cost + 1
  from p
  join edge e on e.n1 = p.n
  where p.path not like '/' || e.n1 || '/'
    and p.n <> 'E'
)
select 'A' as source, 'E' as target, 
  (select min(cost) from p where n = 'E'),
  (select max(cost) from p where n = 'E');

Result:

source  target  min  max 
------- ------- ---- --- 
A       E       2    4   

See running example at DB Fiddle.