sql - Recursion in PostgreSQL -

let's assume have table borders(country1,country2) contains 2 countries border each other, eg. (sweden, norway), etc. find countries can reached given country, sweden, using border crossing only.

here's first part of solution:

with recursive border(countryin) (     select distinct country      (select country2::character varying(4) country            borders country1 = 's'             union             select country1::character varying(4) country            borders country2 = 's' )     union      select distinct sp.country::varchar(4)      (select country1::varchar(4) country, country2 n            borders) sp       join (select country2::varchar(4) country, country1 n, countryin temp            borders, border) st        on sp.country = st.n       , sp.country in st.temp     true               ) select distinct countryin, name  border, country  countryin = code ; 

the thing cannot work how set constraint specific country exists in result border table. tried using and sp.country in st.temp, , several other ways, cannot work.

could 1 give me hint of how can solved?

current results:

• right now, error stating " error: syntax error @ or near "st" line 4: ...s, border) st on sp.country = st.n , sp.country in st.temp "

desired results

• list counties can reached recursively using borders starting 's'. so, if have (s,n), (n,r), (r,c), (d,a), get: (n,r,c)

i believe there room improvement, seem job.

base case, "other" country 's' appear in side

recursive case new country border country in travel path, avoid 1 's' doesnt return origin. include variable track recursive depth doesnt keep looping ever. (dont remember how many country now).

after finish add filter distinct remove duplicate.

maybe include filter on recursive case avoid travel same countries. not sure 1 more efficient.

and (    b.country1 not in (select country travel)      , b.country2 not in (select country travel)     ) 

sql fiddle demo

with recursive travel(r_level, country) (     select distinct 1 r_level,                     case when country1 = 's' country2                          else country1                               end country     borders      country1 = 's'         or country2 = 's'         union      select distinct t.r_level + 1 r_level,                     case when b.country1 = t.country b.country2                          else b.country1                     end country                borders b     join travel  t       on (b.country1 = t.country or b.country2 = t.country)      , (b.country1 <> 's' , b.country2 <> 's')     t.r_level < 300 ) select distinct country travel 

output

| country | |---------| |       n | |       r | |       c | 

please feel free provide more complete sqlfiddle more country improve testing.