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

45
Loading comments…