0

I have this PostgreSQL table with node of a directed graph:

node_id | node_sequence 
-----------------------
   1           1
   2           2 
   3           3

I'd return a table with all the possible origin destination sequence (only in one direction) between the node: (1,2); (1,2,3); (2,3). So the output table should be:

node_id
 ----
   1
   2
   1
   2
   3
   2
   3

Maybe WITH RECURSIVE is the right thing to do but I cannot understand how.

M--
  • 25,431
  • 8
  • 61
  • 93
franco_b
  • 868
  • 3
  • 13
  • 31

1 Answers1

1

Edit from initial answer:
You seem to have 2 constraints you do not mention in your question:

  • You want sequences of at least 2 elements
  • Elements in a sequence must be in ascending order and consecutive

Here is a simple query that does it (CTE GraphNode should be replaced with your table):

WITH RECURSIVE GraphPath AS (
SELECT G2.Node, ARRAY[G1.Node, G2.Node] AS GraphPath /* Start with 2 elements */
FROM GraphNode G1
JOIN GraphNode G2 ON G1.Node + 1 = G2.Node
UNION ALL
SELECT N.Node, P.GraphPath || N.Node
FROM GraphNode N
JOIN GraphPath P ON N.Node = 1 + P.Node 
), GraphNode AS (
SELECT UNNEST(ARRAY[1,2,3]) AS Node
)
SELECT GraphPath
FROM GraphPath
ORDER BY GraphPath
FXD
  • 1,960
  • 1
  • 6
  • 9
  • It' doesn't work. I don't want the "{1,3}" combination – franco_b Nov 16 '18 at 14:27
  • Can you detail the reason why {1,3} should be excluded? To me, it is a valid one (at least since you said "all possible combinations". Is it because 1 and 3 are not consecutive? – FXD Nov 16 '18 at 14:31
  • It's a node graph table. I need all possible link and jump from 1 to 3 is not possible in my system. Sorry maybe my answer is not clear. I just edit it with pair of combination I need – franco_b Nov 16 '18 at 14:33
  • Unless you can provide with another table where the graph edges are stored, there is nothing I can do. There is no operator for things that are only in your brain. – FXD Nov 16 '18 at 14:40
  • Thanks it works. But it's not generalizable query because graph edge is specific for this example. I also have sequence of 100 nodes. – franco_b Nov 16 '18 at 15:20
  • I replace with: GraphEdge AS ( select * from (select a.node as FromNode, b.node AS ToNode from GraphNode a left join GraphNode b on b.node>a.node and b.node::int-a.node::int=1) as foo where ToNode is not null ) – franco_b Nov 16 '18 at 15:26
  • If the constraints was that values had to be consecutive, why did you not say so? You do not need edges for such a case, I am editing the answer for the easy case. – FXD Nov 17 '18 at 03:52