R language simulated row function

Code

The following example creates a simulated row function that takes as input a string representation of time, for example, "12:30:55" and outputs a 3-wide row with the input split it into its component parts.

Enter the following code in the /tmp/split.R file:

nz.fun <- function() {
while(getNext()) {
chunks <- strsplit(getInputColumn(0), ':')[[1]]
for (i in seq(chunks))
setOutputString(i-1, chunks[i])
outputResult()
}
}

Compilation

Compile the code as follows:

/nz/export/ae/utilities/bin/compile_ae --language r --version 3 \
--template compile --user nz --db dev /tmp/split.R

Registration

Because there is no built-in concept of a row function in Netezza SQL, this function is registered as a table function, even though it outputs only one row per input row:

/nz/export/ae/utilities/bin/register_ae --language r --version 3 \
--template udtf --exe split.R --sig "split(VARCHAR(20))" \
--return "TABLE(hour VARCHAR(2), minute VARCHAR(2), second VARCHAR(2))"

Running

When you run this function in nzsql, you get the following result:

SELECT * FROM TABLE WITH FINAL(split('13:22:47'));
HOUR | MINUTE | SECOND
------+--------+--------
13 | 22 | 47