UDSF in Lua: Example 3

Code

Put the following code into /tmp/udsf_example3.nzl:

--[[------------------------------------------------------
ARGTYPE is a global table that contains the type of each argument 
that is to be passed to the evaulate() function. ARGTYPE is set 
before running this UDSF and can be used by any function:

ARGTYPE[1] = type of argument 1
ARGTYPE[2] = type of argument 2
etc.

Lua supports a limited set of data types:
    TYPE_NUMBER       A reguar DOUBLE datatype
    TYPE_TIMESTAMP    Normal unixtime value (seconds since 1970-01-01)
    TYPE_STRING       A string
    
No validation is done on data types in Lua so it is possible to
assign any value to any variable.  

A string can be returned in place of a number as long as the string can 
be converted into a number.  This conversion is done automatically.  
If the string cannot be converted, an error message is issued.

Global variables can be defined for use throughout the program.  Also
note the use here of several pre-defined constants for variable type.

The constants TYPE_NUMBER, TYPE_TIMESTAMP, and TYPE_STRING can be used to 
validate that an argument of the varargs function is of the appropriate type.
--]]----------------------------------------------------------------------

-- The initialize() function validates the data types of each argument
-- that is passed to the evaulate function.
-- The pairs() function lets you iterate though each element in a table.
-- The error() function aborts the program and returns an error message.

function initialize()

    for i,type in pairs(ARGTYPE) do
        if type != TYPE_NUMBER then
            -- use the built in typename function to get the name of the type
            name = typename(type)
            error( string.format( "Argument %d is a %s, not a number!", i, name ), 0)
        end
    end
end

--[[----------------------------------------------------------------------
A varargs UDSF receives all of the arguments that were passed to the UDSF
in the SQL statement.  The "..." argument captures all of the remaining 
arguments to a function call.  

The global variable ARGCOUNT is set to the number of arguments that are 
being passed to the evaluate() function.
--]]----------------------------------------------------------------------

function evaluate( ... )
    -- convert arguments into a table
    argv = { ... }
    x=0
    for i=1,ARGCOUNT do
        x = x + argv[i]
    end
    return x
end

function getType()
    return udf
end

function getName()
    return "vtest"
end

------------------------------------------------------------------------
-- To create a function that accepts variable arguments, return the 
-- first element of the getArgs() definition as VARARGS.
------------------------------------------------------------------------

function getArgs()
    return varargs
end

function getResult()
    return integer
end

Compression

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

Deployment

SQL> IDA DEPLOYUDX SRC /tmp/udsf_example3.tar LANGUAGE lua PROJECT example3

Running

values vtest(2, 10, 30)

1
-----------
         42