Return tbl_lazy object from sql chunk in Rmd

[ramnathv]

@yihui I have a use case for the sql engine where I want to assign the output of the sql query to a variable, but do it lazily without collecting it. This will enable me to use dplyr SQL later down the line to combine these lazy queries.

I am able to get this working with a custom engine (it does NOT handle all the cases handled by eng_sql and is a barebones implementation) that uses dplyr to return an object of class tbl_lazy.

eng_sql_lazy <- function(options){
  print(options)
  code <- paste(options$code, collapse = "\n")
  conn <- get(options$connection)
  varname <- options$output.lazy
  print(varname)
  data <- tbl(conn, dplyr::sql(code))
  if (!is.null(varname)){
    assign(varname, data, envir = knitr::knit_global())
  }
  out <- capture.output(data)
  knitr::engine_output(options, code, out)
}
knitr::knit_engines$set(sql_lazy = eng_sql_lazy)

However, I want to get this working with the sql engine so I can get syntax highlighting as well as the full set of uses cases handled by the engine. Accordingly, I tried to mess with this line of code https://github.com/yihui/knitr/blob/master/R/engine.R#L610, and added a case where it is possible to specify a custom chunk option output.fun.

if (!is.null(varname) && is.null(options$output.fun)) {
  assign(varname, data, envir = knit_global()) 
} else {
  data_out <- options$output.fun(conn, query)
  assign(varname, data_out, envir = knit_global()) 
}

This does NOT seem to work. In fact, anything I try to do within eng_sql does NOT seem to be working.

I would appreciate any pointers on how I could get this feature working. I would be happy to contribute a PR, since I believe this feature would be beneficial to the broader community looking to wrangle SQL in Rmd.

---

By filing an issue to this repo, I promise that

• I have fully read the issue guide at https://yihui.name/issue/.
• I have provided the necessary information about my issue.
  • If I'm asking a question, I have already asked it on Stack Overflow or RStudio Community, waited for at least 24 hours, and included a link to my question there.
  • If I'm filing a bug report, I have included a minimal, self-contained, and reproducible example, and have also included xfun::session_info('knitr'). I have upgraded all my packages to their latest versions (e.g., R, RStudio, and R packages), and also tried the development version: remotes::install_github('yihui/knitr').
  • If I have posted the same issue elsewhere, I have also mentioned it in this issue.
• I have learned the Github Markdown syntax, and formatted my issue correctly.

I understand that my issue may be closed if I don't fulfill my promises.