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.
Edit from initial answer:
You seem to have 2 constraints you do not mention in your question:
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