2

I'm having some trouble when defining aggregate functions. Here’s a reproducible example:

library(MonetDBLite)
library(dplyr)
library(DBI)

dbdir <- tempdir()
con <- dbConnect(MonetDBLite())
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
ms <- src_monetdblite(dbdir)
mt <- tbl(ms, "mtcars")

dbSendUpdate(con, "CREATE AGGREGATE rapi04(val DOUBLE) RETURNS DOUBLE LANGUAGE R {
    aggregate(val, by=list(aggr_group), FUN=median)$x
};")

mt %>% group_by(carb) %>% summarise(rapi04(wt))

Executing this code yields the following error:

Error in .local(conn, statement, ...) : 
  Unable to execute statement 'SELECT *
FROM (SELECT "carb", RAPI04("wt")
FROM (SELECT * FROM "mtcars") AS "_W3"
GROUP BY "carb") A...'.
Server says 'SQLException:SQLparser:Errors encountered in query'.

If I use MonetDB’s median function in the summarise statement it works perfectly, but if I define the function it doesn’t. What is the right way to define an aggregate function inside MonetDBLite?

0 Answers0