Custom function in dplyr psql backend

weighted_mean.R

library(dplyr)
library(RPostgres)

mydb <- src_postgres(dbname = "yourdb"
                     user = "user",
                     password = "password",
                     host = "localhost",
                     port = 5432L)



src_translate_env.tbl_postgres  <- function (x) 
{
  sql_variant(base_scalar,
              sql_translator(.parent = base_agg, 
                             n = function() sql("count(*)"),
                             cor = sql_prefix("corr"), 
                             cov = sql_prefix("covar_samp"),
                             sd = sql_prefix("stddev_samp"), 
                             var = sql_prefix("var_samp"),
                             all = sql_prefix("bool_and"), 
                             any = sql_prefix("bool_or"),
                             paste = function(x, collapse)
                               build_sql("string_agg(", x, ", ", collapse, ")"),
                             weighted.mean = sql_prefix("weighted_mean")),
              base_win)
}

mytbl <- tbl(mydb, "tbl")

mytbl %>%
  summarise(xw = weighted.mean(x, w))