Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table temp_loop as 
select * from (VALUES
(99900405689, 35147645, 1, 1, '{1}'::int[], '{99900405689}'::bigint[]),
(922600, 34461360, 1, 1, '{1}', '{922600}'),
(4039, 3093, 1, 1, '{1}', '{4039}'),
(3069, 3021, 1, 1, '{1}', '{3069}'),
(99901775584, 34782873, 1, 1, '{1}', '{99901775584}'),
(34630979, 34465068, 1, 2, '{2}', '{34630979}'),
(99901516593, 34688475, 1, 1, '{1}', '{99901516593}'),
(99900394949, 34678161, 1, 1, '{1}', '{99900394949}'),
(99901287006, 35189578, 1, 1, '{1}', '{99901287006}'),
(99901008587, 35104382, 1, 1, '{1}', '{99901008587}'),
(32528589, 34477867, 1, 2, '{2}', '{32528589}'))AS v
(pos_id, boss_pos_id, level_num, link_type, link_type_array, pos_id_array);

create index on temp_loop(pos_id);
create index on temp_loop(boss_pos_id);

select * from temp_loop;

SELECT 11



CREATE INDEX



CREATE INDEX




pos_id
boss_pos_id
level_num
link_type
link_type_array
pos_id_array


99900405689
35147645
1
1
{1}
{99900405689}

922600
34461360
1
1
{1}
{922600}

4039
3093
1
1
{1}
{4039}

3069
3021
1
1
{1}
{3069}

99901775584
34782873
1
1
{1}
{99901775584}

34630979
34465068
1
2
{2}
{34630979}

99901516593
34688475
1
1
{1}
{99901516593}

99900394949
34678161
1
1
{1}
{99900394949}

99901287006
35189578
1
1
{1}
{99901287006}

99901008587
35104382
1
1
{1}
{99901008587}

32528589
34477867
1
2
{2}
{32528589}



SELECT 11


create table temp_pos_boss_with_min_link as select * from
(values 
(99900405689, 35147645, 1),
(922600, 34461360, 1),
(4039, 3093, 1),
(3069, 3021, 1),
(99901775584, 34782873, 1),
(34630979, 34465068, 2),
(99901516593, 34688475, 1),
(99900394949, 34678161, 1),
(99901287006, 35189578, 1),
(99901008587, 35104382, 1))AS v
(pos_id, boss_pos_id, link_type);

select * from temp_pos_boss_with_min_link;

SELECT 10




pos_id
boss_pos_id
link_type


99900405689
35147645
1

922600
34461360
1

4039
3093
1

3069
3021
1

99901775584
34782873
1

34630979
34465068
2

99901516593
34688475
1

99900394949
34678161
1

99901287006
35189578
1

99901008587
35104382
1



SELECT 10


with recursive relations_recurs(
    pos_id, boss_pos_id, level_num, link_type, link_type_array, pos_id_array
) as (
    select  l.pos_id,
            l.boss_pos_id,
            1 as level_num,
            l.link_type,
            l.link_type_array,
            l.pos_id_array
    from    temp_loop l
    union all
    select  l.pos_id,
            l.boss_pos_id,
            (r.level_num + 1) as level_num,
            l.link_type,
            (r.link_type_array || l.link_type) as link_type_array,
            (r.pos_id_array || l.pos_id) as pos_id_array
    from    temp_pos_boss_with_min_link l
    join    relations_recurs r
    on      l.pos_id = r.boss_pos_id
    and     l.pos_id <> all (r.pos_id_array)
)
select  distinct pos_id_array[1] as pos_id,
        boss_pos_id as boss_pos_id,
        level_num as level_id,
        pos_id as pos_original_id,
        (case 
            when array[1, 2] <@ t.link_type_array then 0
            when array[1] <@ t.link_type_array then 1
            else 2
        end) link_type
from    relations_recurs t;


pos_id
boss_pos_id
level_id
pos_original_id
link_type


99900394949
34678161
1
99900394949
1

99901008587
35104382
1
99901008587
1

922600
34461360
1
922600
1

99901775584
34782873
1
99901775584
1

4039
3093
1
4039
1

34630979
34465068
1
34630979
2

3069
3021
1
3069
1

99900405689
35147645
1
99900405689
1

32528589
34477867
1
32528589
2

99901516593
34688475
1
99901516593
1

99901287006
35189578
1
99901287006
1



SELECT 11

pos_id	boss_pos_id	level_num	link_type	link_type_array	pos_id_array
99900405689	35147645	1	1	{1}	{99900405689}
922600	34461360	1	1	{1}	{922600}
4039	3093	1	1	{1}	{4039}
3069	3021	1	1	{1}	{3069}
99901775584	34782873	1	1	{1}	{99901775584}
34630979	34465068	1	2	{2}	{34630979}
99901516593	34688475	1	1	{1}	{99901516593}
99900394949	34678161	1	1	{1}	{99900394949}
99901287006	35189578	1	1	{1}	{99901287006}
99901008587	35104382	1	1	{1}	{99901008587}
32528589	34477867	1	2	{2}	{32528589}