Fetching all parents in simple way with PostgreSQL

I have a table with a hierarchical structure:

    _________
    |Plans   |_____________________________________________
    |-------------------------------------------------------|
    | id     | parent | plan_name      | description        |
    |-------------------------------------------------------|
    | 1        0        Painting        bla..bla            |
    | 2        1        Shopping        bla..bla            |
    | 3        1        Scheduling      bla..bla            |
    | 4        2        Costumes        bla..bla            |
    | 5        2        Tools           bla..bla            |
    | 6        2        Paints          bla..bla            | 
    |_______________________________________________________|

I want to list all parents of the plan name Paints, so I can build a breadcrumb to navigate back. Using id = 6 I like to get:

Painting > Shopping > Paints

I’m using postgresql with PHP, and thinking of efficient way to fetch all the parents as simple as possible.

Answer

Use recursive with query:

with recursive pl(id, parent, parents) as (
    select id, parent, array[parent]
    from plans
union
    select pl.id, plans.parent, pl.parents|| plans.parent
    from pl
    join plans on pl.parent = plans.id
    )
select distinct on (id) id, parents
from pl
order by id, array_length(parents, 1) desc

 id | parents
----+---------
  1 | {0}
  2 | {1,0}
  3 | {1,0}
  4 | {2,1,0}
  5 | {2,1,0}
  6 | {2,1,0}
(6 rows)

SqlFiddle

Instead of integer array of parent ids you can use text column to aggregate plan names:

with recursive pl(id, parent, parents, depth) as (
    select id, parent, plan_name, 0
    from plans
union
    select pl.id, plans.parent, plans.plan_name|| ' > ' ||pl.parents, depth+ 1
    from pl
    join plans on pl.parent = plans.id
    )
select distinct on (id) id, parents
from pl
order by id, depth desc;

 id |            parents
----+--------------------------------
  1 | Painting
  2 | Painting > Shopping
  3 | Painting > Scheduling
  4 | Painting > Shopping > Costumes
  5 | Painting > Shopping > Tools
  6 | Painting > Shopping > Paints
(6 rows)