Recursive PostgreSQL Query for Updating Column

Question

Hello I'm trying to update database column recursively with SQL query.

I have 2 tables module and module_dependency.

First table module has columns (with example data):

id, name, state,
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"

Second table module_dependency has columns (with example data):

 id | dependency_name | module_id 
----+-----------------+-----------
  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

I need to change state of website module to "to upgrade" with all of it's depended modules.

I can check current module's state:

SELECT * FROM module WHERE name = 'website' AND state = 'installed';

 id |  name   |   state   
----+---------+-----------
  1 | website | installed

To get all dependencies of website module I use query:

SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'website' AND m.state = 'installed';

  name   | dependency_name | module_id 
---------+-----------------+-----------
 website | website_sale    |         1
 website | website_blog    |         1
 website | website_online  |         1

That means website_sale, website_blog, and website_online is depended on module_id 1 which is website.

The main problem is that website_online is depended on crm and crm on account module.

SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'website_online' AND m.state = 'installed';

      name      | dependency_name | module_id 
----------------+-----------------+-----------
 website_online | crm             |        10

SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'crm' AND m.state = 'installed';

 name | dependency_name | module_id 
------+-----------------+-----------
 crm  | account         |         3

I need recursively change state of all website dependencies to "to upgrade". Updated rows should look like this:

id, name, state,
1, "website", "to upgrade"
3, "crm", "to upgrade"
5, "account", "to upgrade"
6, "website_sale", "to upgrade"
8, "website_blog", "to upgrade"
10, "website_online", "to upgrade"

---

You can create tables with test data:

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);

---

I have tried to write recursive query, but unsuccessfully. Looks like infinite loop. If I could get all module ids of module table, I could simply update selected entries.

WITH RECURSIVE modules_to_upgrade AS
(
    SELECT id
    FROM module
    WHERE name = 'website'

        UNION ALL

    SELECT md.module_id
    FROM module_dependency md JOIN modules_to_upgrade mtu on mtu.id = md.module_id
)
select * from modules_to_upgrade;

Answer 1

To link the recursive part back to modules it seems you can't use the dependency id.

So it has to be on the names.

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 

db<>fiddle here