UDAF in Lua: Example 2

Code

Put the following code into the file /tmp/udaf_example2.nzl:

--[[-------------------------------------------------------------------
This UDAF returns the value in a column that is in the same row as the maximum 
value of another column in the same table.

For example "select max2(x,y) from table1" returns the value in column x that 
is in the row that contains the maximum value in column y.

This UDAF takes, as input, two arguments of type char(255) and returns a 
single value of type char(255). You can use this UDAF as a template to create 
additional UDAFs for other data types. To do this, modify the definitions of 
the arguments, state variables, and result that are defined by the getArgs(), 
getState(), and getResult() methods.
------------------------------------------------------------------------------]]

local X=1
local Y=2

function accumulate(s, x, y)
    if x == null or y == null then
        return null
    end

    if s[X] == null then
        s[X] = x
        s[Y] = y
        return s
    end

    -- returning null leaves the state variables unchanged
    if s[Y] > y then
        return null
    end

    if s[Y] < y then
        s[X] = x
        s[Y] = y
        return s
    end

    if s[X] < x then
        s[X] = x
        return s
    end

    -- returning null leaves the state variables unchanged
    return null
end

function merge(s1, s2)

    -- returning null leaves the state variables unchanged
    if s1[Y] > s2[Y] then
        return null
    end

    if s1[Y] < s2[Y] then
        return s2
    end

    if s1[X] < s2[X] then
        return s2
    end

    -- returning null leaves the state variables unchanged
    return null
end

function finalResult(s)
    return s[X]
end

function getName()
    return "max2"
end

function getType()
    return "uda"
end

function getArgs()
    args={}
    args[X] = { "x", char(255) }
    args[Y] = { "y", char(255) }
    return args
end

function getState()
    state={}
    state[X] = { "", char(255) }
    state[Y] = { "", char(255) }
    return state
end

function getResult()
    return char(255)
end

Compression

Compress the file /tmp/udaf_example2.nzl into a file with the name /tmp/udaf_example2.tar.

Deployment

SQL> IDA DEPLOYUDX SRC /tmp/udaf_example2.tar LANGUAGE lua PROJECT example2

Running

SELECT * FROM table1

COL1         COL2         COL3                
------------ ------------ -------
Harry        Potter       9                
Hermione     Granger      6                       
Ronald       Weasley      9
Albus        Dumbledore   3   
Tom          Riddle       2

SELECT max2(col1,col2) FROM table1

MAX2                                                                                                                                                                                                              
-----------------------------------------------------------------
Ronald