Recursive stored functions in MySQL
Solution 1
MySQL does not allow recursive FUNCTIONs, even if you set max_sp_recursion_depth.
It does allow up to 255 recursion in a PROCEDURE if you set max_sp_recursion_depth.
So I recommend that you replace your function with a procedure, using an INOUT variable for the return_path.
Solution 2
From the stored procedure in your question, *with the help from @Ike Walker,
DROP PROCEDURE IF EXISTS getPath;
DELIMITER $$
CREATE PROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT)
BEGIN
DECLARE parent_id INT UNSIGNED;
DECLARE path_result TEXT;
SET max_sp_recursion_depth=50;
SELECT CONCAT('/', ac.name), ac.parent_id INTO return_path, parent_id FROM article_categories AS ac WHERE ac.id = category_id;
IF parent_id > 0 THEN
CALL getPath(parent_id, path_result);
SELECT CONCAT(path_result, return_path) INTO return_path;
END IF;
END $$
DELIMITER ;
Create a function:
DROP FUNCTION IF EXISTS getPath;
CREATE FUNCTION getPath(category_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE res TEXT;
CALL getPath(category_id, res);
RETURN res;
END$$
Next, you can select:
SELECT category_id, name, getPath(category_id) AS path FROM article_categories ;
tirithen
System developer with focus on JavaScript (mostly excited about Polymer 3) and Go. Always eager to learn new things.
Updated on July 09, 2022Comments
-
tirithen almost 2 years
I'm trying to make a function that recursively builds a path for a specific category
CREATE FUNCTION getPath(inId INT) RETURNS TEXT DETERMINISTIC BEGIN DECLARE return_path TEXT; DECLARE return_parent_id INT; SELECT CONCAT('/', name) INTO return_path FROM article_categories WHERE id = inId; SELECT parent_id INTO return_parent_id FROM article_categories WHERE id = inId; IF return_parent_id > 0 THEN SELECT CONCAT(getPath(return_parent_id), return_path) INTO return_path; END IF; RETURN return_path; END
When I try to run this function with a category that has no parents (parent_id = 0) it works fine but when I try a category that has a parent_id > 0 I get 1424 Recursive stored functions and triggers are not allowed.
How do I work around this? I'm going to host this code on a regular web hosting service that should have at least MySQL server version 5.1.
After some help from Ike Walker I have made a precedure instead that works fine
DROP PROCEDURE IF EXISTS getPath; DELIMITER // CREATE PROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT) BEGIN DECLARE parent_id INT UNSIGNED; DECLARE path_result TEXT; SET max_sp_recursion_depth=50; SELECT CONCAT('/', ac.name), ac.parent_id INTO return_path, parent_id FROM article_categories AS ac WHERE ac.id = category_id; IF parent_id > 0 THEN CALL getPath(parent_id, path_result); SELECT CONCAT(path_result, return_path) INTO return_path; END IF; END // DELIMITER ;
I then use something like this to call it
CALL getPath(72, @temp); SELECT @temp;
-
tirithen over 13 yearsThank you for sorting this out, I have now made a procedure instead:
-
tirithen over 13 yearsDROP PROCEDURE IF EXISTS getPath; DELIMITER // CREATE PROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT) BEGIN DECLARE parent_id INT UNSIGNED; DECLARE path_result TEXT; SET max_sp_recursion_depth=50; SELECT CONCAT('/', ac.name) INTO return_path FROM article_categories AS ac WHERE ac.id = category_id; SELECT ac.parent_id INTO parent_id FROM article_categories AS ac WHERE ac.id = category_id; IF parent_id > 0 THEN CALL getPath(parent_id, path_result); SELECT CONCAT(path_result, return_path) INTO return_path; END IF; END // DELIMITER ;