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.)

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.