Is it possible to return multiple values from mysql function?

Question

I have three tables and I want to sum all the result columns of each table and return these values from the function I define to do all the process. Is it possible to return those three float summation from a single mysql function ?

Vestment

hw did you tried... ? – Heirship

This seems more like something you'd do with a stored procedure, not a function. – Ras

@Prix What's the syntax for returning multiple values from a function, and how would you use it in the expression that calls the function? – Ras

MySQL doesn't have arrays. – Ras

@Prix A function is called like SELECT somefun(arguments) AS columnTotals. He wants somefun to return 3 separate values. My understanding is that functions have to be single-valued, that can't become multiple columns. – Ras

@Ras makes sense, I was completely thinking of something else. – Idea

@Barman takes the problem in a correct way, but I guess from his commend no way to return 3 differnt values from a single function. But I can define a extra input argument to function defining which table is the target and join the result as different columns – Vestment

@Ras has earned the above title, barman! – Idea

Why do people so often type Barman when SO's name completion will get the name right? There's another user named Barman, I hope he doesn't mind getting so many of my notifications. – Ras

@Ras I typed to him :) – Vestment

Answer 1

The correct way to do this would be using a stored procedure:

CREATE PROCEDURE stored_proc(IN arg1 INT, IN arg2 INT, OUT retVal1 FLOAT, OUT retVal2 FLOAT, OUT retVal3 FLOAT)

You can then assign the variables with

SELECT x*y INTO retVal1;
...

and call the stored procedure with @variables to access them:

CALL stored_proc(1, 2, @retVal1, @retVal2, @retVal3);

Geniculate

Answer 2

My dirty solution is: 1. Concatenating values in a string. 2 returns string. 3 Splits returned string to values. I assume that it is not elegant and I sure this have limitations but it works for simple cases

Also is necessary create the splitting function because Mysql has not this function:

First edit your function.

CREATE FUNCTION yourFunctionWith2valuesForReturning()
BEGIN
     DECLARE var1 VARCHAR(255);
     DECLARE var2 VARCHAR(255);

    // Your function logic here. Assign values to var1 and var2

    RETURN CONCAT_WS('|', var1, var2);
END

Now you can split your returned value calling this function:

CREATE FUNCTION SPLIT_STR(x VARCHAR(510), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END

For getting the values

SET value1 = SPLIT_STR(returnedVAlue, '|', 1);
SET value2 = SPLIT_STR(returnedVAlue, '|', 2);

Notes:

You can use a string as delimiter, f.e: '|sep|' or another you want.

SPLIT_STR function is cortesy of http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

About splitting a string in mysql see also this question: Split Varchar into Character in MySQL

Wolbrom

You can simplify the SPLIT_STR: return substring_index(substring_index(x, delim, pos), ',', -1); – Purpose

Just a word of warning to those looking at this as a solution... If any of the column values can contain user-entered data, be sure that your separator is NOT valid for those columns, otherwise you will have issues correctly parsing the data. – Sundstrom

Answer 3

"MySQL stored function returns only one value" BUT, with a trick, it is possible to obtain multiple results from a function that can be used, AFTER its call, in other parts of the SQL code:

CREATE FUNCTION `return_multi`(
    `txt` VARCHAR(50)
)
RETURNS varcHAR(50)
LANGUAGE SQL
NOT DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT 'A trick to get multiple values ​​from a function'

BEGIN
set @return_multi_length=length(txt);
set @return_multi_upper=upper(txt);
set @return_multi_lower=lower(txt);
return concat(upper(substring(txt,1,1)),lower(substring(txt,2)));
END

Now call the function:

SELECT return_multi('vicTor') as RES;

RES
Victor

Then get other values:

SELECT
 @return_multi_length as LEN
,@return_multi_upper AS UP
,@return_multi_lower AS LOW;

LEN	UP	LOW
6	VICTOR	victor

Rodina

Answer 4

Just use a JSON return and put whatever you want in it.

Girlhood