CODEWITHSUNDEEP
mysqlargumentsuser-defined-functionsvariadic-functions
Alain
Alain

Reputation: 36954

Create a MySQL stored function with a dynamic number of arguments

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

Upvotes: 7

Views: 9750

Answers (3)

SomeOne_1
SomeOne_1

Reputation: 1012

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);

Upvotes: 0

Marc Alff
Marc Alff

Reputation: 8395

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

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562348

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

Upvotes: 6

Related Questions