Recursive SQL query in Postgres

Question

Scema:

Person(pid:int,
       name:char(20),
       predecessor:int -> Person.pid
      )

Example Table:

pid, name, predecessor
0,   'abc', NULL
1,   'bcd', 0
2,   'cde', 1
3,   'efg', NULL
4,   'fgh', 3

How do i find all successors of a Person 'abc'?

Desired Ouput:

name 
'bcd'
'cde'

Many Thanks!

Please edit the question, providing sample data and desired results. — Gordon Linoff, Aug 03 '14 at 12:11
Check the manual. It has examples for that: http://www.postgresql.org/docs/9.3/static/queries-with.html — , Aug 03 '14 at 12:32

score 1 · Accepted Answer · answered Aug 03 '14 at 12:37

You can do this by generating all the ancestors and then filtering them out. The following is an example for your data:

with recursive cte(pid, lev, ancestor) as (
      select pid, 0, predecessor
      from person p 
      union all
      select cte.pid, lev + 1, p.predecessor
      from person p join
           cte
           on p.pid = cte.ancestor
     )
select p2.name
from cte join
     person p1
     on cte.ancestor = p1.pid join
     person p2
     on cte.pid = p2.pid
where p1.name = 'abc';

Here is a SQL Fiddle.

score 0 · Answer 2 · edited May 23 '17 at 10:09

Just generate the rows you need (not all ancestors for all rows) in a recursive CTE:

with recursive cte as (
   select p.pid, p.name, 1 AS lvl
   from   person a
   join   person p ON p.predecessor = a.pid
   where  a.name = 'abc'

   union all
   select p.pid, p.name, c.lvl + 1
   from   cte    c
   join   person p ON  p.predecessor = c.pid
   )
select name
from   cte
order  by lvl;

SQL Fiddle.

Aside: You don't want to use char(20). Just use text.

Recursive SQL query in Postgres

2 Answers2