Postgresql: Limiting number of recursions
Hello,
I have the following table "knows" in my database. pID1 and pID2 stands for person1 knows person2 and vice versa. Formally:
knows: [pID1, pID2]
Now, I would like to query all first-,second- and third-degree acquaintances of pID1 = 10. For instance, what I mean by second-degree acquaintance is all pIDs that are known by the acquaintances of pID1=10.
(I hope I've made myself clear)
Since I am working with recursion, "all Id have to do" is to limit how often my query runs recursively.
Here is my code:
with recursive
acquaintances(a,d) as (select pid1 as a, pid2 as d from knows
union
select acquaintances.a, knows.pid2 as d
from acquaintances, knows
where acquaintances.d = knows.pid1)
select a from acquaintances where d = 1
As you will see, my code would not stop after querying the third-degree of acquaintances and continue instead. Once again, the question is how do I limit the number of recursive calls in postgresql
Just a side note, for those interested: I used example 1 of this video as a reference
Posted Dec 16, 2018, 11:18 PM UTC