PIPELINED Functions - Oracle to MariaDB Migration

In Oracle a user-defined function (UDF) can return multiple records (a table-valued function), enabling you to query the function directly in the FROM clause like a regular table.

MariaDB does not support table-valued functions but you can use JSON arrays and JSON functions to implement them.

Oracle:

  -- Define the table type
  CREATE TYPE strings AS TABLE OF VARCHAR2(100);
  /
 
  -- Pipelined function returning multiple rows
  CREATE OR REPLACE FUNCTION get_rows(p_rows IN NUMBER) 
    RETURN strings PIPELINED AS
  BEGIN
    FOR i IN 1 .. p_rows LOOP
      PIPE ROW(CHR(i + 64) || i);   
    END LOOP;
  END;
  /
 
  -- Use the function in FROM
  SELECT * FROM get_rows(7);

Result:

In MariaDB you can use JSON_ARRAY_APPEND and JSON_OBJECT functions to create a JSON array returned by a function:

MariaDB:

  DELIMITER //
 
  -- Emulate table-valued function by returning JSON array
  CREATE FUNCTION get_rows(p_rows INT)
  RETURNS LONGTEXT
  DETERMINISTIC
  BEGIN
    DECLARE data LONGTEXT DEFAULT '[]';
    DECLARE i INT DEFAULT 1;
 
    WHILE i <= p_rows DO
      SET data = JSON_ARRAY_APPEND(data, '$', JSON_OBJECT('column_value', CONCAT(CHAR(i + 64), i)));
      SET i = i + 1;  
    END WHILE;
 
    RETURN data;
  END
  //
 
  DELIMITER ;

Now you can invoke this function using JSON_TABLE function as follows:

MariaDB:

  -- Using JSON_TABLE function to convert JSON array back to rows
  SELECT *
  FROM JSON_TABLE(get_rows(7), '$[*]'
             COLUMNS(column_value VARCHAR(100) PATH '$.column_value')) t;

Result:

For more information, see Oracle to MariaDB Migration.