Additional R language simple table functions

To better understand the R AE concepts, consider the following examples.

Table function example 1

This example creates a function that returns the total length of character input columns and the total sum of all numeric input columns for each input row.

Code

Enter the following code in the /tmp/sampleudtf.R file:
nz.fun <- function () {
while(getNext()) {
tl <- 0
ts <- 0
for (i in seq(inputColumnCount())) {
x <- getInputColumn(i-1)
if (is.character(x)) {
tl <- tl + nchar(x)
}
if (is.numeric(x)) {
ts <- ts + as.double(x)
}
}
setOutputInt32(0, tl)
setOutputDouble(1, ts)
outputResult()
}
}

Compilation

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

Registration

Register the AE as follows:
/nz/export/ae/utilities/bin/register_ae --language r --version 3 --user nz \
--db dev --template udtf --sig 'rtotal(VARCHAR(1024),DOUBLE)' \
--return 'TABLE(length INT4, sum DOUBLE)' --exe sampleudtf.R

Running

Run the UDTF as follows:
SELECT * FROM TABLE WITH FINAL(rtotal('text', 1));
LENGTH | SUM
--------+-----
4 | 1
(1 row)

Table Function Example 2

This example shows multiple output rows. Because the total length of character columns is an

Code

Enter the following code in the /tmp/sampleudtf.R file:
nz.fun <- function () {
while(getNext()) {
tl <- 0
ts <- 0
for (i in seq(inputColumnCount())) {
x <- getInputColumn(i-1)
if (is.character(x)) {
tl <- tl + nchar(x)
}
if (is.numeric(x)) {
ts <- ts + as.double(x)
}
}
setOutputString(0, 'total length of character columns')
setOutputDouble(1, tl)
outputResult()
setOutputString(0, 'total sum of numerics')
setOutputDouble(1, ts)
outputResult()
}
}

integer, and the total sum of numeric is of type double, choose the most flexible data type for the second output column based on the data types of the inputs, in this case double. The R code differs slightly for this example.

Compilation

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

Registration

Register the AE as follows. Due to the difference in output, you must specify a different output signature during the registration.
/nz/export/ae/utilities/bin/register_ae --language r \
--version 3 --user nz --db dev --template udtf \
--sig 'rtotal(VARCHAR(1024),DOUBLE)' \
--return 'TABLE(name VARCHAR(64), value DOUBLE)' \
--exe sampleudtf.R

Running

When you call this function, the following output is produced:
SELECT * FROM TABLE WITH FINAL(rtotal('text', 1));
NAME | VALUE
-----------------------------------+-------
total length of character columns | 4
total sum of numerics | 1
(2 rows)

Table Function Example 3

By changing the registration command from the previous example, you can create a function of dynamic input signature by using VARARGS.

The R Code and Compilation steps are the same as in the previous example.

Registration

Register the AE as follows:
/nz/export/ae/utilities/bin/register_ae --language r --version 3 \
--user nz --db dev --template udtf --sig 'rtotal(VARARGS)' \
--exe sampleudtf.R --return 'TABLE(name VARCHAR(64), value DOUBLE)'

Running

You can now pass any number of columns to R:
SELECT * FROM TABLE WITH FINAL(rtotal('text', 1, 'second text',
2, 3, 4, 'the last text'));
NAME | VALUE
-----------------------------------+-------
total length of character columns | 28
total sum of numerics | 10
(2 rows)