Recursive stored functions in MySQL
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.
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 DETERMINISTICBEGIN 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 ;