Postgres 10

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

CREATE TABLE module(id integer, name text, state text);
INSERT INTO module VALUES
(1, 'website', 'installed'),
(2, 'purchase', 'installed'),
(3, 'crm', 'installed'),
(4, 'sale', 'uninstalled'),
(5, 'account', 'installed'),
(6, 'website_sale', 'installed'),
(7, 'purchase_bonus', 'installed'),
(8, 'website_blog', 'installed'),
(9, 'sale_discount', 'installed'),
(10, 'website_online', 'installed');

CREATE TABLE module_dependency(id integer, dependency_name text, module_id integer);
INSERT INTO module_dependency VALUES
(1, 'website_sale', 1),
(2, 'sale_bonus', 4),
(3, 'website_blog', 1),
(4, 'sale_discount', 4),
(5, 'website_online', 1),
(6, 'crm', 10),
(7, 'account', 3);
10 rows affected

7 rows affected

WITH RECURSIVE rcte_modules_to_upgrade AS
(
    SELECT 
      m.id as module_id
    , m.name as module_name
    , md.id as dependency_id
    , md.dependency_name
    , m.id as base_module_id
    , 1 as depth
    FROM module m
    JOIN module_dependency md 
      ON md.module_id = m.id
    WHERE m.name = 'website'

    UNION ALL

    SELECT 
      m.id
    , m.name
    , md.id
    , md.dependency_name
    , rcte.base_module_id
    , rcte.depth + 1
    FROM rcte_modules_to_upgrade rcte
    JOIN module m
      ON m.name = rcte.dependency_name
    LEFT JOIN module_dependency md 
      ON md.module_id = m.id
)
select * 
from rcte_modules_to_upgrade;


module_id
module_name
dependency_id
dependency_name
base_module_id
depth


1
website
1
website_sale
1
1

1
website
3
website_blog
1
1

1
website
5
website_online
1
1

6
website_sale
null
null
1
2

8
website_blog
null
null
1
2

10
website_online
6
crm
1
2

3
crm
7
account
1
3

5
account
null
null
1
4


WITH RECURSIVE rcte_modules_to_upgrade AS
(
    SELECT 
      m.id as module_id
    , md.dependency_name
    FROM module m
    JOIN module_dependency md 
      ON md.module_id = m.id
    WHERE m.name = 'website'

    UNION ALL

    SELECT 
      m.id
    , md.dependency_name
    FROM rcte_modules_to_upgrade rcte
    JOIN module m
      ON m.name = rcte.dependency_name
    LEFT JOIN module_dependency md 
      ON md.module_id = m.id
)
update module
set state = 'to upgrade'
where id in (select distinct module_id 
             from rcte_modules_to_upgrade);
6 rows affected

select * from module order by id;


id
name
state


1
website
to upgrade

2
purchase
installed

3
crm
to upgrade

4
sale
uninstalled

5
account
to upgrade

6
website_sale
to upgrade

7
purchase_bonus
installed

8
website_blog
to upgrade

9
sale_discount
installed

10
website_online
to upgrade

module_id	module_name	dependency_id	dependency_name	base_module_id	depth
1	website	1	website_sale	1	1
1	website	3	website_blog	1	1
1	website	5	website_online	1	1
6	website_sale	null	null	1	2
8	website_blog	null	null	1	2
10	website_online	6	crm	1	2
3	crm	7	account	1	3
5	account	null	null	1	4

id	name	state
1	website	to upgrade
2	purchase	installed
3	crm	to upgrade
4	sale	uninstalled
5	account	to upgrade
6	website_sale	to upgrade
7	purchase_bonus	installed
8	website_blog	to upgrade
9	sale_discount	installed
10	website_online	to upgrade