HHAsJ9XD

1create database test;
2DROP TABLE IF EXISTS edge;
3DROP TABLE IF EXISTS node;
4
5-- create Tables
6CREATE TABLE node (
7  id text  NOT NULL ,
8  color varchar(32) NOT NULL,
9  PRIMARY KEY (id)
10);
11CREATE TABLE edge (
12  source text NOT NULL REFERENCES node (id),
13  target text NOT NULL REFERENCES node (id),
14  PRIMARY KEY (source, target)
15);
16
17-- insert data
18INSERT INTO node (id, color) VALUES
19(1, 'blue'),
20(2, 'white'),
21(3, 'green'),
22(4, 'green'),
23(5, 'white'),
24(6, 'green'),
25(7, 'white'),
26(8, 'white'),
27(9, 'white'),
28(10, 'green'),
29(11, 'white');
30
31INSERT INTO edge (source, target) VALUES
32(1, 2),
33(1, 4),
34(1, 5),
35(2, 3),
36(3, 5),
37(3, 9),
38(3, 10),
39(4, 5),
40(5, 8),
41(6, 7),
42(6, 8),
43(7, 8),
44(7, 11),
45(8, 11),
46(9, 10);
47
48with recursive pfad(von,nach,länge,folge) as (
49	select source,target,1,
50	source || ','|| target from edge		
51	union all	
52	select p.von, e.target, p.länge+1, 
53	p.folge ||','|| e.target	
54	from edge e join pfad p	
55	on p.nach = e.source 
56)
57select * from pfad
58where von = '1' and nach = '11';