R language simple table function

Code

This example creates a table function that takes a variable number of string representations of time that is separated by commas, for example, "12:30:55,4:20:18”. The function outputs one row for each time-representation. Each row displays the time split into its component parts.

Enter the following code in the /tmp/split_many.R file:
nz.fun <- function() {
while(getNext()) {
input <- getInputColumn(0)
chunks <- strsplit(input, ',')[[1]]
times <- strsplit(chunks, ':')
for (tm in times) {
for (i in seq_along(tm))the following output
setOutputString(i-1, tm[1])
outputResult()
}
}
}

Compilation

Compile the R source file as follows:
/nz/export/ae/utilities/bin/compile_ae --language r --version 3 \
--template compile --user nz --db dev /tmp/split_many.R

Registration

Register the Table Function AE as follows:
/nz/export/ae/utilities/bin/register_ae --language r --version 3 \
--template udtf --exe split_many.R --sig "split_many(VARCHAR(20))" \
--return "TABLE(hour VARCHAR(2), minute VARCHAR(2), second VARCHAR(2))" \
--db dev --user nz

Running

When you run the registered AE, the following sample output is produced:
SELECT * from TABLE WITH FINAL(split_many('13:22:47,22:12:45,03:22:09'));
HOUR | MINUTE | SECOND
------+--------+--------
13 | 22 | 47
22 | 12 | 45
03 | 12 | 45
(3 rows)