MySQL: How to write a recursive function to show menu tree? I try to build a menu tree inside the SQL with this function:

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(_typeId INT, _typeText TEXT)
  RETURNS TEXT
BEGIN
    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    SELECT parent_type_id, title INTO _parentId, _typeContent 
    FROM fcom_product_type WHERE id = _typeId;

    RETURN CONCAT( IF(_parentId IS NOT NULL,' / ', 'not found'), _typeContent);
END;
$$
DELIMITER ;


If I want call my function again in case it finds a "parent", MySQL says the follow error to me.

DROP FUNCTION IF EXISTS hello_world;
DELIMITER $$
CREATE FUNCTION hello_world(_typeId INT, _typeText TEXT)
  RETURNS TEXT
BEGIN
    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    SELECT parent_type_id, title INTO _parentId, _typeContent 
    FROM fcom_product_type WHERE id = _typeId;


    RETURN CONCAT(
      IF(_parentId IS NOT NULL, 
         hello_world(_parentId, CONCAT(_typeContent, ' / ') ), 
         'not found'), 
      _typeContent, ' - ', _parantId);
END;
$$
DELIMITER ;


Error: Recursive stored functions and triggers are not allowed.
MySql doesn't support recursive functions. It supports recursive stored procedures but this feature is disabled by default. See manual.

Recursion uses stack space to store contexts and tends to put memory pressure into server. I strongly discourage to use stored procedures to do this kind of calculation, better to do them into the application code. Anyway to achieve your goal you must unroll recursion into an equivalent iterative loop. I tried to figure out what was the output of the function. I renamed function from hello_world to build_path: 

DELIMITER $$
CREATE FUNCTION build_path(_typeId INT)
  RETURNS TEXT
BEGIN

    DECLARE _parentId INT;
    DECLARE _typeContent TEXT;

    set @ret = '';
    while _typeId is not null do

      SELECT parent_type_id, title 
      INTO  _parentId,  _typeContent 
      FROM fcom_product_type 
      WHERE id = _typeId;

      IF(found_rows() != 1) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'id not found in table fcom_product_type';
      END IF;

      set @ret := CONCAT('/', _typeContent, @ret); 
      set _typeId := _parentId;

    end while;

    RETURN @ret;
END;
$$
DELIMITER ;


So you can call:

call build_path(2)


to get for example:

/root/child1/child2