[SOLVED] R2DBC adjacency list get all children

R2DBC adjacency list get all children

I have a table which has id and parentId columns: i refer to this structure as an adjacency list.
So, now i want to get all children of arbitrary id. Classic solution of this problem use recursion, for example here is Postgres procedure or CTE implementation.

I'm currently using Spring Webflux and Spring Data R2DBC + Postgres R2DBC driver (which doesn't support stored procedures yet).
How can i approach this problem in reactive style? Is it even possible or am i missing something conceptually wrong?

UPD 1:
Let's image we've data like:

+-------------+---------+
|id           |parent_id|
+-------------+---------+
|root         |NULL     |
|id1          |root     |
|dir1         |root     |
|dir1_id1     |dir1     |
|dir1_dir1    |dir1     |
|dir1_dir1_id1|dir1_dir1|
+-------------+---------+

Now i want to have a method inside a ReactiveCrudRepository, which will return all children of provided id.

For example, using sample data: by providing id='dir1', i want to get children with ids: ['dir1_id1', "dir1_dir1", "dir1_dir1_id1"].

Solution

using proc or cte has nothing to do with full scan.

in your case scenario, you only have to use recursive cte , but adding an index on id, parentid will surely help

create index idx_name on tablename (parentid , id);

also 10k rows its not that big , index will definitely improve cte alot.