Call R functions from sqldf queries

Question

Is there a way to call R functions from sqldf queries? E.g.

sqldf("select paste('Hello', 'World')")

Or, is there a way to define custom functions or stored procedures within the SQLite engine behind sqldf? (I am using sqldf with plain old in-memory R data frames; I'm not connecting to any actual databases.)

Merci

Why would you use R functions rather than the built in SQLite functions? – Treillage

I think the answer is simply, no. – Phylogeny

joran: actually, the particular function I'm interested in is digest() from the digest R package, to compute MD5 hashes – Merci

Ok, then I agree with @nograpes. – Treillage

If you want to call R code inside SQL queries then use PostreSQL plus pl/r and if you want to do it from R check the RPostgreSQL packages (also possible with sqldf) – Carlstrom

I suppose, then, that the workaround is to create additional columns in my dataframe e.g. mydf["hashedpassword"] <- digest(mydf$password) – Merci

Oops, need an sapply: mydf["hashedpassword"] <- sapply(mydf$password, digest(x)) – Merci

Answer 1

1) Existing Functions First make sure that the function you want is not already available. For example the code in the question is directly supported in SQL already:

> sqldf("select 'Hello' || ' ' || 'world' ")
  'Hello' || ' ' || 'world'
1               Hello world

2) RSQLite.extfuns One has all the SQL functions from sqlite's version of SQL plus a large number of user defined functions out of the box listed in ?initExtension in the RSQLite package.

3) Other Loadable Extensions Functions in any existing sqlite loadable extensions can be loaded via the sqlite SQL function load_extension(). e.g. see these extensions

4) Custom functions Custom functions can be added to SQLite but they must be written in C.

5) PostgreSQL & sqldf sqldf supports not just sqlite but also h2, postgresql and mysql. postgresql is particularly powerful in this respect. See this link from the postgresql documentation Also see Pl/R and R Embedded Postgres package.

6) H2 & sqldf The H2 database is supported by sqldf. Like sqlite H2 is included right in the RH2 driver R package so you don't have to install a separate database; however, you do have to install Java. It has a builtin SHA256 hash function (called hash).

7) mix sqldf & R sql and R can be mixed like this:

library(digest)
transform(sqldf("select * from BOD"), digest = sapply(demand, digest))

8) Other See this SO question and answers

UPDATE: Added info on H2.

Slather