Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tree (
  id_reach integer PRIMARY KEY,
  from_node integer UNIQUE NOT NULL,
  to_node integer,
  area float
);

INSERT INTO tree(id_reach, from_node, to_node, area) VALUES
 (0, 1, 0, 40),
 (1, 3, 1, 29),
 (2, 2, 1, 5),
 (3, 4, 3, 7),
 (4, 5, 3, 6);

CREATE TABLE



INSERT 0 5


WITH RECURSIVE us_path AS (
   -- start with biggest area for each node
   (  -- parentheses required
   SELECT DISTINCT ON (to_node)
          to_node AS starting_node, from_node, ARRAY[to_node, from_node] AS path
   FROM   tree
   ORDER  BY to_node, area DESC NULLS LAST
   )
   UNION ALL
   SELECT tr.*
   FROM   us_path usp
   CROSS  JOIN LATERAL (
      SELECT usp.starting_node, tr.from_node, usp.path || tr.from_node
      FROM   tree tr
      WHERE  tr.to_node = usp.from_node
      ORDER  BY tr.area DESC NULLS LAST
      LIMIT  1  -- chose biggest area at each junction
      ) tr
   )
SELECT DISTINCT ON (starting_node)
       starting_node, path
FROM   us_path
ORDER  BY starting_node, cardinality(path) DESC;


starting_node
path


0
{0,1,3,4}

1
{1,3,4}

3
{3,4}



SELECT 3


-- alternative with correlated subquery, and arregation in outer SELECT
WITH RECURSIVE us_path AS (
   -- start with biggest area for each node
   (  -- parentheses required
   SELECT DISTINCT ON (to_node)
          to_node AS starting_node, from_node, 1 AS step
   FROM   tree
   ORDER  BY to_node, area DESC NULLS LAST  -- start with biggest area for each node
   )
   UNION ALL
   SELECT usp.starting_node
        ,(SELECT tr.from_node
          FROM   tree tr
          WHERE  tr.to_node = usp.from_node
          ORDER  BY tr.area DESC NULLS LAST
          LIMIT  1)  -- chose biggest area at each junction
        , step + 1
   FROM   us_path usp      
   WHERE  usp.from_node IS NOT NULL
   )
SELECT starting_node, starting_node || array_agg(from_node)
FROM  (
   SELECT *
   FROM   us_path
   WHERE  from_node IS NOT NULL
   ORDER  BY starting_node, step
   ) sub
GROUP  BY 1;


starting_node
?column?


0
{0,1,3,4}

1
{1,3,4}

3
{3,4}



SELECT 3