20220203.iterative.sql

DROP TABLE IF EXISTS edges, nodes;

CREATE TABLE nodes
(
  id INTEGER PRIMARY KEY
);

CREATE TABLE edges
(
  "from" INTEGER NOT NULL REFERENCES nodes(id),
  "to" INTEGER NOT NULL REFERENCES nodes(id)
);

INSERT INTO nodes SELECT generate_series(1,5);
INSERT INTO edges VALUES
(1,2),(2,3),(3,5),(3,4),(4,3),(3,1),(5,1);

CREATE OR REPLACE FUNCTION walk_graph(param_product_id INTEGER)
  RETURNS TABLE (id INTEGER)
  LANGUAGE plpgsql
AS $$
DECLARE
  var_queue INTEGER[] := ARRAY[param_product_id];
  var_product_ids INTEGER[] := ARRAY[param_product_id];
  var_iteration_product_ids INTEGER[];
  var_product_id INTEGER;
BEGIN

  WHILE array_length(var_queue, 1) > 0 LOOP
    RAISE NOTICE 'stack = %', var_queue;
  
    var_product_id := var_queue[array_upper(var_queue, 1)];
    var_queue := var_queue[1:array_upper(var_queue, 1) - 1];    
      
    var_iteration_product_ids := ARRAY(SELECT "to" FROM edges
      WHERE "from" = var_product_id
      AND NOT("to" = ANY(var_product_ids)));     
   
    var_product_ids := var_product_ids || var_iteration_product_ids;
    var_queue := var_queue || var_iteration_product_ids;  
  END LOOP;

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

SELECT * FROM walk_graph(3);