R-language: using UDF in SQL statements

Question

I am new to R, and I am trying to use my UDF in a SQL statement (via the sqldf function). However, I am getting an error saying no such function

Does sqldf allow using UDFs?

Here is a code example:

mySum <- function(x,y) { ret <- x+y return(ret) } 
data1=data.frame("var1"=1:3, "var2" = 5:7) 
View(data1) 
sSQL1 = "SELECT var1,var2,mySum(var1,var2) AS sum FROM data1" 
data2 <- sqldf(sSQL1,stringsAsFactors = FALSE) View(data2)

On running I am getting an error message:

Error in result_create(conn@ptr, statement) : no such function: mySum

Please include a [mcve] of your code. It's really hard to debug something that we can't even see. — divibisan, Oct 10 '18 at 18:38
here is a code example: mySum <- function(x,y) { ret <- x+y return(ret) } data1=data.frame("var1"=1:3, "var2" = 5:7) View(data1) sSQL1 = "SELECT var1,var2,mySum(var1,var2) AS sum FROM data1" data2 <- sqldf(sSQL1,stringsAsFactors = FALSE) View(data2) ******* On running I am getting an error message: Error in result_create(conn@ptr, statement) : no such function: mySum — cadol_askUbu, Oct 10 '18 at 19:26
Please edit your question to add relevant information. You can use the `edit` button under the tags — divibisan, Oct 10 '18 at 20:21
I did add the OP's sample code from the above comment into their question. — Bear, Oct 16 '18 at 15:19

G. Grothendieck · Answer 1 · 2018-10-12T12:23:54.820

SQLite extensions

If you are using the RSQLite backend then RSQLite includes a number of funtions written in C that can be used from SQL and sqldf makes these available. See ?initExtensions in the RSQLite package. If one of these already does what you want then that could be a solution.

Adding SQL functions

Of the varous backends supported SQLite lets one add C functions, H2 lets one add Java functions and MySQL and PostgreSQL allow one to add functions written in SQL using CREATE FUNCTION.

Text preprocessing of SQL statement

backticks In general, you can't mix R into your SQL statements; however, you can perform text preprocessing of the SQL by using fn$sqldf(...) and then using $ for single value replacements or backquotes for replacing the result of an R expression. For example,

library(sqldf)

mySum <- function(x, y) paste(x, y, sep = "+")
fn$sqldf("select Time, demand, `mySum('Time', 'demand')` total from BOD", verbose = TRUE)

giving the following where I added an arrow at the end of the line that shows the statement actually passed to the backend.

sqldf: library(RSQLite)
sqldf: m <- dbDriver("SQLite")
sqldf: connection <- dbConnect(m, dbname = ":memory:")
sqldf: initExtension(connection)
sqldf: dbWriteTable(connection, 'BOD', BOD, row.names = FALSE)
sqldf: dbGetQuery(connection, 'select Time, demand, Time+demand total from BOD') <--
sqldf: dbDisconnect(connection)
  Time demand total
1    1    8.3   9.3
2    2   10.3  12.3
3    3   19.0  22.0
4    4   16.0  20.0
5    5   15.6  20.6
6    7   19.8  26.8

See ?fn in the gsubfn package and note that the gsubfn package is automatically made available by sqldf so you don't need a library statement for it.

dollar sign The above was an example using backticks. Here is an example of using $ to do the same thing. The output would be the same.

mySum <- function(x, y) paste(x, y, sep = "+")
total <- mySum("Time", "demand")
fn$sqldf("select Time, demand, $total total from BOD")

R-language: using UDF in SQL statements

1 Answers1

SQLite extensions

Adding SQL functions

Text preprocessing of SQL statement