createSQLFunction: Register an R function or C routine as an SQL Function

In duncantl/RSQLiteUDF: Ability to register R functions and C routines as SQLite functions

Description

These functions allow us to register R functions or C routines with an SQLite database so that the functions can be used directly within SQL queries as scalar and aggregate functions. This allows us to augment the functionality within SQL directly with R functions, or by C code in different DLLs/DSOs (compiled libraries). We can register scalar functions that return a value for each record on which they are called (e.g., log), and also aggregate functions that process multiple records and finally return a single result (e.g. average).

Usage

createSQLFunction(db, func, nargs = -1L, name = substitute(func),
                   isRoutine = is.character(func), userData = NULL)
createSQLAggregateFunction(db, step, final, nargs = -1L, name = substitute(step),
                            isRoutine = FALSE, userData = NULL) 

Arguments

db	the database in which the function is being registered
func	the scalar function or routine being registered. This can be specified as an R function, or as the address of a C routine, or as a description of a C routine obtained by getNativeSymbolInfo. The caller can also specify a character string to specify the name of a C routine. This is less desirable than calling getNativeSymbolInfo as there is ambiguity as to how this is found and resolved.
step	part of an aggregate function. This is the per-record R function that is called for each record.
final	this is the final step in an aggregate function. This is the R function that is called to compute the final result having processed all the records in the step function.
name	the name by which this function can be called in SQL
nargs	the number of arguments that should be specified in each call to this function. If this is -1, the function can be called with different numbers of arguments, i.e., it is variadic. This is more flexible and general, but has a slightly higher overhead. For aggregate functions, nargs is the number of inputs to the record/step function. The final function is called with no inputs.
isRoutine	a logical value, with TRUE indicating that func is to be interpreted as a compiled routine. This then interprets func as the name of the routine and uses getNativeSymbolInfo to find this routine.
userData	data that is passed to the C routines each time they are called. This is typically an externalptr object referencing a native data structure.

Value

These functions are used for their side effects of registering the functions to be used in SQL queries.

Author(s)

Duncan Temple Lang

See Also

sqliteExtension dbGetQuery

Examples

library(RSQLite)

db = dbConnect(SQLite(), ":memory:")

ptr = getNativeSymbolInfo("myfloorFunc")$address
createSQLFunction(db, ptr, 1L, "myfloor")
dbGetQuery(db, "SELECT myfloor(2.3);")

# We can refer to a C routine by name and leave getNativeSymbolInfo()
# resolve it (without knowing the DLL/DSO). This is potentially "dangerous"!
createSQLFunction(db, "lastNChars", 2L)
dbGetQuery(db, "SELECT lastNChars('USA:22 September 1986', 4);")


createSQLFunction(db, nchar, 1L, "numChars")
dbGetQuery(db, "SELECT numChars('bob');")
dbGetQuery(db, "SELECT numChars('Robert');")

duncantl/RSQLiteUDF documentation built on Jan. 5, 2021, 7:55 a.m.