Postgresql Recursive With

Using a WITH inside of a RECURSIVE WITH in PostgreSQL

Start with WITH RECURSIVE. You can still add non-recursive CTEs:

WITH RECURSIVE
   num  AS (VALUES (50))
,  num2 AS (SELECT column1 * 2 AS value FROM num)
,  t(n) AS (
      VALUES (1)
      UNION ALL
      SELECT n+1 FROM t WHERE n < (SELECT value FROM num2)
   )
SELECT sum(n) FROM t;

sqlfiddle

See:

• How to use multiple CTEs in a single SQL query?

Recursion with postgresql query

This query should help. The first CTE ("with" part) provides all relations there are in the table. The second CTE filters only end child records out.

And the final part - the main query - searches for parents having 331 as a child and shows you those.

with recursive result_q as (
  select 1 debug_step,  parent_data, child_data 
    from data_rel
  union all
  select debug_step + 1, q.parent_data, r.child_data
    from data_rel r
    join result_q q
      on q.child_data = r.parent_data 
),
parent_end_childs as (
  select q.parent_data, q.child_data 
    from result_q q
   where not exists (select 1 from data_rel r where r.parent_data = q.child_data)
   order by parent_data, child_data)
   
select pec.*
  from parent_end_childs pec
  join parent_end_childs pec_1
    on pec.parent_data = pec_1.parent_data
 where pec_1.child_data = 331

dbfiddle

SQL - WITH RECURSIVE doesn't work, when there is another query before

recursive refers to the entire with clause. So just use:

with recursive path as (<some other query>),                                      -- 2

What recursive really means is that Postgres will search for "table" names first as CTEs and then as tables/views in the recursive part of the CTE. The issue is really resolving identifiers. It has no effect on other CTEs.

Recursive PostgreSQL Query for Updating Column

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

PostgreSQL recursive select find root element from leaf

With the current data structure, there is no other way than to visit the entire path from leaf to root. You can speed up this search a bit by operating only on key columns:

with recursive my_tree as (
    select id, parent_id
    from messages 
    where id = 9
union all
    select m.id, m.parent_id 
    from messages m
    join my_tree t on m.id = t.parent_id
) 
select m.*
from messages m
join my_tree t using(id)
where t.parent_id is null

db<>fiddle.

If this solution is not satisfactory, you can consider adding a column to the table

alter table messages add root_id int;

Filling this column with a recursive query will be easy and you will have immediate access to the root of every node.

An alternative is to change the data structure using the ltree extension.

recursive query in postgres with a parameter in chain

You can provide the parameter in the root/anchor query of the CTE. But you need to select from the actual table in the recursive part:

WITH RECURSIVE chain AS (
  select "applicationNodeInstanceId", "nextNodeInstanceId"
  from appnodeinstance     
  where "applicationNodeInstanceId" = 'vc1' --<< this can be a parameter
  UNION all
  SELECT c."applicationNodeInstanceId", c."nextNodeInstanceId"
  FROM appnodeinstance c
    JOIN chain ani ON c."applicationNodeInstanceId" = ani."nextNodeInstanceId"
)
SELECT * 
FROM chain;

---

Related Topics