sql - WITH RECURSIVE query to choose the longest paths -

i new with recursive in postgresql. have reasonably standard recursive query following adjacency list. if have, example:

1 -> 2 2 -> 3 3 -> 4 3 -> 5 5 -> 6 

it produces:

1 1,2 1,2,3 1,2,3,4 1,2,3,5 1,2,3,5,6 

what have just:

1,2,3,4 1,2,3,5,6 

but can't see how in postgres. seem "choose longest paths" or "choose paths not contained in path". can see how join on itself, seems quite inefficient.

an example query is:

with recursive search_graph(id, link, data, depth, path, cycle) (    select g.id, g.link, g.data, 1, array[g.id], false    graph g   union    select g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = any(path)    graph g, search_graph sg    g.id = sg.link , not cycle ) select * search_graph; 

you have solution @ fingertips cycle, add predicate @ end.

but adjust break condition 1 level, appending 1 node many:

with recursive search (    select id, link, data, array[g.id] path, (link = id) cycle      graph g     not exists (       select 1         graph        link = g.id       )     union    select g.id, g.link, g.data, s.path || g.id, g.link = any(s.path)      search s    join   graph g on g.id = s.link     not s.cycle    ) select *   search where cycle; -- cycle not false;  -- alternative if link can null 

• also including start condition mentioned @wildplasser.

• init condition cycle (link = id) catch shortcut cycles. not necessary if have check constraint disallow in table.

• the exact implementation depends on missing details.

• this assuming graphs terminated cycle or link null , there fk constraint link id in same table. exact implementation depends on missing details. if link not link (no referential integrity), need adapt ...