Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog Usage of PostgreSQL Recursive Queries in Group Merging

Usage of PostgreSQL Recursive Queries in Group Merging

This short article reviews PostgreSQL recursive queries in group merging with examples.

By digoal

Background

Enter a val1 and find the corresponding gids. There are other valN in those gids, and val1 and valN belong to the same group.

Loop

It finds the corresponding gids based on valN. There are other valM in those gids.

It finds the other gids based on valM. There are other valX in those gids.

...

End Loop

All vals are merged into a larger group.

It can be implemented using PostgreSQL recursive syntax.

Example

create table a (gid text, val int);  
insert into a values ('g1',101);  
insert into a values ('g1',102);  
insert into a values ('g2',102);  
insert into a values ('g2',103);  
insert into a values ('g3',102);  
insert into a values ('g3',103);  
insert into a values ('g3',104);  
insert into a values ('g4',101);  
insert into a values ('g4',105);  
insert into a values ('g4',103);  
insert into a values ('g5',103);  
insert into a values ('g5',106);  
insert into a values ('g5',107);  
insert into a values ('g6',108);  
insert into a values ('g7',109);  
insert into a values ('g8',110);  
insert into a values ('g9',111);  
insert into a values ('g9',106);  
insert into a values ('g10',112);  
insert into a values ('g10',107);  
insert into a values ('g11',113);  
insert into a values ('g11',101);  
insert into a values ('g12',114);  
insert into a values ('g12',104);  
with recursive tmp as (  
select 1 as level, array_agg(row(gid,val)::a) as path from a  
where   
gid in (select gid from a where val=102)    
group by level  
  
union all    
  
select t1.level, t1.path   
from   
  (select * from tmp) t ,   
  LATERAL (   
  select t.level+1 as level, array_cat(t.path, array_agg(row(a.gid,a.val)::a)) as path   
  from a   
    where a.gid in ( select a.gid from a where a.val in ( select unnest.val from unnest(t.path) ) )  
      and a.gid not in (select unnest.gid from unnest(t.path))   
      -- and a.val not in (select unnest.val from unnest(t.path))   
      group by (t.level+1)  
  ) t1   
)   
select * from tmp;    
 level |                                                                                                                      path                                                                                      
                                    
-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------  
     1 | {"(g1,101)","(g1,102)","(g2,102)","(g2,103)","(g3,102)","(g3,103)","(g3,104)"}  
     2 | {"(g1,101)","(g1,102)","(g2,102)","(g2,103)","(g3,102)","(g3,103)","(g3,104)","(g4,101)","(g4,105)","(g4,103)","(g5,103)","(g5,106)","(g5,107)","(g11,113)","(g11,101)","(g12,114)","(g12,104)"}  
     3 | {"(g1,101)","(g1,102)","(g2,102)","(g2,103)","(g3,102)","(g3,103)","(g3,104)","(g4,101)","(g4,105)","(g4,103)","(g5,103)","(g5,106)","(g5,107)","(g11,113)","(g11,101)","(g12,114)","(g12,104)","(g9,111)","(  
g9,106)","(g10,112)","(g10,107)"}  
(3 rows)  
with recursive tmp as (  
select 1 as level, array_agg(row(gid,val)::a) as path from a  
where   
gid in (select gid from a where val=102)    
group by level  
  
union all    
  
select t1.level, t1.path   
from   
  (select * from tmp) t ,   
  LATERAL (   
  select t.level+1 as level, array_cat(t.path, array_agg(row(a.gid,a.val)::a)) as path   
  from a   
    where a.gid in ( select a.gid from a where a.val in ( select unnest.val from unnest(t.path) ) )  
      and a.gid not in (select unnest.gid from unnest(t.path))   
      -- and a.val not in (select unnest.val from unnest(t.path))   
      group by (t.level+1)  
  ) t1   
)   
select * from tmp order by level desc limit 1;  
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
level | 3  
path  | {"(g1,101)","(g1,102)","(g2,102)","(g2,103)","(g3,102)","(g3,103)","(g3,104)","(g4,101)","(g4,105)","(g4,103)","(g5,103)","(g5,106)","(g5,107)","(g11,113)","(g11,101)","(g12,114)","(g12,104)","(g9,111)","(g9,106)","(g10,112)","(g10,107)"}
PostgreSQL Database Recursive Query Group Merging
0 0 0
Share on

Read previous post:

How PostgreSQL Returns a Dynamic Number of Columns: Row-Column Convert

Read next post:

Partition Table of PostgreSQL Hash and Partition ID Calculation

digoal

282 posts | 24 followers

You may also like

Comments

digoal

282 posts | 24 followers

Related Products

More Posts by digoal

See All