20220203.optimized.sql

CREATE OR REPLACE FUNCTION walk_graph(param_node_id INTEGER)
  RETURNS TABLE (id INTEGER)
  LANGUAGE plpgsql
AS $$
DECLARE
  var_node_ids INTEGER[] := ARRAY[param_node_id];
  var_iteration_node_ids INTEGER[] := ARRAY[param_node_id];
BEGIN

  WHILE array_length(var_iteration_node_ids, 1) > 0 LOOP  
      
    var_iteration_node_ids := ARRAY(SELECT DISTINCT "to" FROM edges
      WHERE "from" = ANY(var_iteration_node_ids)
      AND NOT("to" = ANY(var_node_ids)));     
   
    var_node_ids := var_node_ids || var_iteration_node_ids;
  END LOOP;

  RETURN QUERY SELECT unnest(var_node_ids);
END $$;