UDTF in Lua: Example 1

This example creates a function called split_to_rows that splits a character string into rows based on the position, in the string, of a delimiter character.

Code

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

--[[------------------------------------------------------------
Split a string into rows based on the position of a delimiter character.

USAGE:
    table(split_to_rows(string,delim))

EXAMPLES:

1. Select from a table
        select * from foo, table(split_to_rows(foo.str, ','))

2. Select from a literal value
        select * from table(split_to_rows('Kansas-Lawrence-Lenexa','-'))

-----------------------------------------------------------------------]]

function processRow(str,delim)
    t = split(str,delim)
    if t ==  nil then
        t = { str }
    end
    return nil
end

row={}
function outputRow(rownum)
    row[2] = t[rownum]
    if row[2] == nil then return nil end

    row[1] = rownum

    return row
end

function getType()
    return udtf
end

function getName()
    return "split_to_rows"
end

function getArgs()
    local args={}
    args[1] = { "", varchar(any) }
    args[2] = { "", varchar(any) }
    return args
end

function getShape()
    local shape={}
    shape[1] = { "pos", integer      }
    shape[2] = { "str", varchar(255) }
    return shape
end

Compression

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

Deployment

Deploy the UDX to a Db2® instance by issuing the following CLPPlus command:

SQL> IDA DEPLOYUDX SRC /tmp/udtf_example1.tar LANGUAGE lua PROJECT example1

Running

Use this function in a SELECT statement, for example:

SELECT * from table(split_to_rows('Kansas-Lawrence-Lenexa','-'))"

POS          STR
----------- --------------------------------------------------
          1 Kansas
          2 Lawrence 
          3 Lenexa