Recursive Stored Functions In Mysql

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

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,

DROPPROCEDURE IF EXISTS getPath;
DELIMITER $$
CREATEPROCEDURE getPath(IN category_id INT UNSIGNED, OUT return_path TEXT)
BEGINDECLARE 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 >0THENCALL getPath(parent_id, path_result);
        SELECT CONCAT(path_result, return_path) INTO return_path;
    END IF;
END $$
DELIMITER ;

Create a function:

DROPFUNCTION IF EXISTS getPath;
CREATEFUNCTION getPath(category_id INT) RETURNS TEXT DETERMINISTICBEGINDECLARE 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 ;