Create a MySQL stored function with a dynamic number of arguments

Question

I am trying to create a MySQL function IS_IN_ENUM('value', 'val1', 'val2', 'val3') which return true if 'value' is in ('val1', 'val2', 'val3'). I know I can do SELECT 'value' IN ('val1', 'val2', 'val3') but that's less intersting because I just want to learn how to create such functions.

I give you an example, consider the following ADD function :

CREATE FUNCTION my_add (
    a DOUBLE,
    b DOUBLE
)
RETURNS DOUBLE
BEGIN

    IF a IS NULL THEN
        SET a = 0;
    END IF;

    IF b IS NULL THEN
        SET b = 0;
    END IF;

    RETURN (a + b);
END;

If I do SELECT my_add(1, 1), I get 2 (wow!).

How can I improve this function to be able to call :

SELECT my_add(1, 1); -- 2
SELECT my_add(1, 1, 1); -- 3
SELECT my_add(1, 1, 1, 1); -- 4
SELECT my_add(1, 1, 1, 1, 1, 1, .....); -- n

one question, can't you pass multiple params in one param? `MYFUNCT(('1','2','3'))`? — Timo Huovinen, Jul 14 '15 at 14:30
You can pass a SET, such as `MYFUNCT("1,2,3")` and then play with the SET-ready functions. But that's not really friendly. — Alain Tiemblo, Jul 14 '15 at 14:36

score 6 · Accepted Answer · answered Dec 09 '12 at 18:16

The function example you show is a Stored Function, not a UDF. Stored Functions in MySQL don't support a variable number of arguments, as @Enzino answered.

MySQL UDFs are written in C or C++, compiled into dynamic object files, and then linked with the MySQL server with a different syntax of CREATE FUNCTION.

See http://dev.mysql.com/doc/refman/5.5/en/adding-udf.html for details of writing UDFs. But I don't know if you want to get into writing C/C++ code to do this.

MySQL UDFs do support variable number of arguments. In fact, all UDFs implicitly accept any number of arguments, and it's up to you as the programmer to determine if the number and datatypes of the arguments given are valid for your function.

Processing function arguments in UDFs is documented in http://dev.mysql.com/doc/refman/5.5/en/udf-arguments.html

Nope, I do want to find the MySQL equivalent of the C `varargs`, MySQL does such a function with `CONCAT` for example, which can take as much arguments as we want. If that's possible of course. Thank you for pointing it out the difference between Stored Functions and UDFs, I already read that somewhere but I forgot. — Alain Tiemblo, Dec 09 '12 at 18:28
`CONCAT()` is implemented in C as a built-in function. There are numerous built-in functions with variable args. I myself contributed the `SHA2()` function, which supports two arguments, the second of which is optional. But to do so, it had to be implemented in C. — Bill Karwin, Dec 09 '12 at 18:50

score 1 · Answer 2 · answered Dec 10 '12 at 14:32

I am trying to create a MySQL function IS_IN_ENUM('value', 'val1', 'val2', 'val3') which return true if 'value' is in ('val1', 'val2', 'val3').

For this you can use the native function FIELD:

http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_field

IS_IN_ENUM means FIELD != 0.

Check also FIND_IN_SET

http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_find-in-set

Stored functions do not support variable number of parameters.

Now, if you really want to implement such a native function yourself in the MySQL server code, look for subclasses of Create_native_func in sql/item_create.cc

score 0 · Answer 3 · answered Jul 15 '14 at 08:30

0

Old question but you don need to create is_in_enum since that is already build in. simply do select true from table where value IN (1,2,3,4);

answered Jul 15 '14 at 08:30

SomeOne_1

808
10
10

Create a MySQL stored function with a dynamic number of arguments

3 Answers3

Linked

Related