RECURSIVE QUERIES IN POSTGRESQL 8.4 BETA Originally posted http://pastie.org/539924

Destination Types

 id |     name     
----+--------------
  1 | COUNTRY
  2 | ISLAND GROUP
  3 | TOWN
  4 | ISLAND
(4 rows)

Destinations

 id | parent_id |          name          | destination_type_id 
----+-----------+------------------------+---------------------
  1 |           | SPAIN                  |                   1
  2 |         1 | BALEARIC ISLANDS       |                   2
  7 |         1 | CANARY ISLANDS         |                   2
  3 |         2 | IBIZA                  |                   4
  4 |         2 | MENORCA                |                   4
  5 |         2 | MALLORCA               |                   4
  6 |         2 | FORMENTERA             |                   4
  8 |         7 | TENERIFE               |                   4
  9 |         7 | GRAN CANARIA           |                   4
 10 |         7 | FUERTEVENTURA          |                   4
 11 |         7 | LA PALMA               |                   4
 12 |         7 | LANZAROTE              |                   4
 14 |         8 | PUERTO DE LA CRUZ      |                   3
 15 |         8 | SANTA CRUZ DE TENERIFE |                   3
(14 rows)

Output

peter@sfm:~/SQL$ psql destinations_test < subdestinations.sql 
 depth |          name          |     name     
-------+------------------------+--------------
     1 | CANARY ISLANDS         | ISLAND GROUP
     2 | TENERIFE               | ISLAND
     2 | FUERTEVENTURA          | ISLAND
     2 | GRAN CANARIA           | ISLAND
     2 | LANZAROTE              | ISLAND
     2 | LA PALMA               | ISLAND
     3 | PUERTO DE LA CRUZ      | TOWN
     3 | SANTA CRUZ DE TENERIFE | TOWN
(8 rows)

subdestinations.sql

with recursive subdestination(n) as (

  	--first get the originating node
	select 	
  		1 as depth,* 
	from 		
  		destinations 
	where 	
  		name = 'CANARY ISLANDS'

  union all

  	--now recursively the relations
  	select 
  		n+1,d.* 
	from 
  		destinations as d
	inner join subdestination as sd
  		on d.parent_id = sd.id
)


select 
	sd.n as depth, sd.name, dt.name
from 
	subdestination sd
inner join destination_types as dt 
	on sd.destination_type_id = dt.id
order by 
	depth asc;