how to return table from mariadb function?

Question

i'm having a sql function which returns a table with 2 columns i would like to write the same function for mariadb. I am unable to do it from workbench, its throwing a syntax error so i am trying to do it from linux terminal.

Create Function testing (numb INT) Return INT AS table tbl(id INT, val INT) BEGIN DECLARATION id INT, val INT; SET id=10; SET id=122; END

Create Function testing (numb INT) Return INT AS table tbl(id INT, val INT) BEGIN DECLARATION id INT, val INT; SET id=10; SET id=122; END

I expect the table as output.

Answer 1

MariaDB does not support table-valued functions.

As of MariaDB 10.6.0 (April 2021), the best way to practically achieve this is probably with the aid of JSON_OBJECTAGG() and JSON_TABLE() to pass tabular data between the function and its caller in the form of JSON. The function should JSON_OBJECTAGG() the result set and return the resulting JSON, then the caller can JSON_TABLE() this JSON to get back the original result set.