`tmp_tbl()` create a database-backed tbl that gets dropped when the handle goes out of scope

tmp-tbl.r

library(dplyr)
library(dbplyr)
library(uuid)

tmp_tbl <- function(x, con, tbl_name = UUIDgenerate()) {
  uuid <- UUIDgenerate()
  db_copy_to(con, tbl_name, as.data.frame(x), temporary = TRUE)
  ret <- tbl(con, tbl_name)
  ret$info <- new.env()
  ret$info$con <- con
  ret$info$name <- tbl_name
  reg.finalizer(
    ret$info,
    function(e) dbRemoveTable(e$con, e$name)
  )
  ret
}

# Test it with duckdb
library(DBI)
library(duckdb)

con <- dbConnect(duckdb())
dbcars <- tmp_tbl(mtcars, con)
dbListTables(con)
rm(dbcars)
gc()
dbListTables(con)