Recursive stored functions in MySQL

Question

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;

Right now I'm developing on Ubuntu with MySQL-Server version: 5.1.41-3ubuntu12.6 (Ubuntu) — tirithen, Sep 20 '10 at 13:57
I have found http://forums.mysql.com/read.php?98,224107,224638#msg-224638 that talks about SET max_sp_recursion_depth=N; where N is the number of recursions to allow. But I still get 1424 Recursive stored functions and triggers are not allowed. — tirithen, Sep 20 '10 at 14:01

score 12 · Accepted Answer · answered Sep 20 '10 at 14:56

12

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.

answered Sep 20 '10 at 14:56

Ike Walker

62,183
14
104
106

Thank you for sorting this out, I have now made a procedure instead: – tirithen Sep 21 '10 at 01:34
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) 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 ; – tirithen Sep 21 '10 at 01:35

score 6 · Answer 2 · answered Mar 11 '17 at 10:16

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 ;

Recursive stored functions in MySQL

2 Answers2

Linked