Indefinite number of arguments for SQL User Defined Functions?

Question

In Oracle and MySQL, how can I create a function that takes an indefinite number of parameters so that it may be called like GREATEST(value1,value2,...)?

Comparing two values by a certain standard is pretty easy but passing the "greater" value to yet another comparison is what I don't seem to get work in SQL.

Thanks!

Edit (after Mike's comment below): I am looking for a solution for comparing multiple columns. In concrete terms, my question is how to implement GREATEST() as a UDF. The following code compares three columns.

SELECT CASE WHEN CASE WHEN col_1 < col_2 THEN col_2
                 ELSE col_1 END < col_3 THEN col_3
       ELSE CASE WHEN col_1 < col_2 THEN col_2
                 ELSE col_1 END END AS greatest
  FROM figures;

Apparently, this does not scale so well. It'll be much more useful to have a general function that applies the same comparison method over and over to a list of values.

By SQL I mean any SQL database product but I prefer a solution that works in Oracle or MySQL

Is this the same as - select * from, order by x desc/asc The max value must the first/last ? Even in XSLt, I just sort things to get max value ? — MikeyKennethR, Nov 14 '11 at 10:34
Presumably you mean SQL Server - SQL (without further qualification) refers to a language, implemented to varying degrees by various different products, and each with their own unique extensions also. — Damien_The_Unbeliever, Nov 14 '11 at 10:59
Here is one way that scales a lot better the the `case` statement. http://stackoverflow.com/questions/7995945/how-to-i-modify-this-t-sql-query-to-return-the-maximum-value-for-different-colum/7996068#7996068 — Mikael Eriksson, Nov 14 '11 at 12:02
How many columns are you trying to compare? If it is not many, you could use `SELECT Max(Col4, Max(Col3, MAX(Col1, Col2))) FROM myTable`. — shahkalpesh, Nov 14 '11 at 14:36
It's a pity that IN does not work with comparison operators and ALL and ANY with expression/column list. MAX/MIN functions don't work across columns either. But that is a great idea. How about nesting a GREATER (or LESSER) function like below? `DELIMITER // CREATE FUNCTION greater(i1 int, i2 int) RETURNS int BEGIN DECLARE o int; CASE WHEN i1 >= i2 THEN SET o = i1; ELSE SET o = i2; END CASE; RETURN o; END //` — amemus, Nov 15 '11 at 00:15

Chris Townsend · Accepted Answer · 2011-11-18T19:05:43.060

In Oracle (instances that support unpivot)

SELECT MyID, MAX(GreatestVal) 
FROM figures
  UNPIVOT (
          GreatestVal 
          FOR MyID 
          IN (col_1, col_2, col_3,...)
          );

Oracle query is untested because I don't have an instance handy Further details on unpivot are located here and it does the same thing as the SQL Server unpivot.

MySQL I'm unsure of how to do this in MySQL but may investigate as I have opportunity (unless someone beats me to it. ;-) )

The following are SQL Server answers:

Doing it in a UDF is not pretty because all of the input parameters would be REQUIRED in every case. If you could get away with implementing it as a stored procedure then you could specify default values for the input parameters and call it with a dynamic number of columns. Either way, you'll have to decide on a maximum number of input parameters. Here is an example in UDF on SQL Server:

SELECT dbo.GREATESTof3(col_1, col_2, col_3)
FROM figures;

CREATE FUNCTION GREATESTof3(@col_1 sql_variant = null, @col_2 sql_variant = null, @col_3 sql_variant = null)
RETURNS sql_variant
AS 
BEGIN
    DECLARE @GreatestVal sql_variant
    DECLARE @ColumnVals TABLE (Candidate sql_variant)


    INSERT INTO @ColumnVals
    SELECT @col_1
    UNION ALL
    SELECT @col_2
    UNION ALL
    SELECT @col_3

    SELECT @GreatestVal = MAX(Candidate)
    FROM @ColumnVals

    RETURN @GreatestVal
END

This would require a new UDF for each variant of the number of input parameters OR creating one that could take a greater number but then in the call to the UDF you would have to either specify some value for each unused parameter (null) or specify default.

Alternatives:

This gives you the max value of the three columns from the whole table and would be easier to have a dynamic number of columns:

SELECT MAX([Value]) AS Greatest
FROM figures
UNPIVOT
(
    [Value]
    FOR ColumnName IN ([Col_1], [Col_2], [Col_3])
) AS unpvt

Assuming you have some rowid or another column that you would want in the output so that you could get the greatest from the specified columns for each row you could do something like this:

SELECT RowID, MAX([Value]) AS Greatest
FROM figures
UNPIVOT
(
    [Value]
    FOR ColumnName IN ([Col_1], [Col_2], [Col_3])
) AS unpvt 
GROUP BY RowID

score 0 · Answer 2 · answered Nov 17 '11 at 21:00

Another SQL Server option (not sure how well it'll translate to MySQL/Oracle).

I've had to do this with a list of integer IDs before that I put into a comma-delimited list and fed to a function to get the greatest:

CREATE Function [dbo].[GreatestFromList]
(@ListOfValues VARCHAR(8000))

RETURNS INT

AS

BEGIN

DECLARE @ListOfValuesTable TABLE (ValueColumn INT) 

    DECLARE @spot1 SMALLINT, @str1 VARCHAR(8000) 

    WHILE @ListOfValues <> ''  
    BEGIN  
        SET @spot1 = CHARINDEX(',', @ListOfValues)  
        IF @spot1>0  
            BEGIN  
                SET @str1 = LEFT(@ListOfValues, @spot1-1) 
                SET @ListOfValues = RIGHT(@ListOfValues, LEN(@ListOfValues)-@spot1)  
            END  
        ELSE  
            BEGIN  
                SET @str1 = @ListOfValues 
                SET @ListOfValues = ''  
            END  
        INSERT INTO @ListOfValuesTable (ValueColumn) VALUES(convert(int, @str1)) 
    END  

DECLARE @GreatestValue INT

SELECT @GreatestValue = SELECT MAX(ValueColumn) FROM @ListOfValuesTable

RETURN @GreatestValue 

END

Indefinite number of arguments for SQL User Defined Functions?

2 Answers2