Created
April 8, 2022 19:02
-
-
Save mvodep/558f49c0ef358be9267999802b6e2243 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment