Can we get a sqlValues() function like this ...

[petermeissner]

One thing I often build when constructing SQL-statements is a list of SQL values.

Example SQL

Let' have some examples. Although, they are not thaaaaat useful they show different areas of use. While the "INSERT INTO" can also be done with the sqlAppendTable()-function the latter one cannot.

CREATE TABLE my_table (a INT, b INT);
CREATE TABLE my_table_sum (sum_a INT);

/* example 1 */
INSERT INTO my_table VALUES (1,2), (3,4), (5,6);

/* example 2 */
WITH temp_table1 as (
  SELECT my_table.a FROM (VALUES (1,7), (3,6), (2,6)) as tmp_table2 (a, b)
  LEFT JOIN my_table on my_table.a = tmp_table2.a
)
INSERT INTO my_table_sum (sum_a) 
	(SELECT sum(temp_table1.a) from temp_table1)
;

I think sqlValues() is missing and would be a great addition to 'DBI making (a) a lot of users happy and (b) providing an essential building block for flexibly and more savely () assembling SQL-statements in R.

Furthermore, it might be more expressive and therefore more readable drop in for internal usages like in sqlAppendTable():

DBI/R/table-insert.R

Lines 46 to 54 in 6f6f616

	# Convert fields into a character matrix
	rows <- do.call(paste, c(sql_values, sep = ", "))
	SQL(paste0(
	"INSERT INTO ", table, "\n",
	" (", paste(fields, collapse = ", "), ")\n",
	"VALUES\n",
	paste0(" (", rows, ")", collapse = ",\n")
	))
	}

To make the argument even more convincing I prepared code that would provide a S4-version of the function already working with bothe single vectors and data.frame-like objects.

#' hidden aliases
#' @name hidden_aliases
NULL

#' sqlValues
#'
#' A function to generate SQL value lists, to be used with sql values keyword:
#'   "\code{VALUES (1,2), (1,3), (2,7)}".
#'
#' @param conn A database connection.
#' @param x a vector or data.frame like
#' @param ... additional parameters passed through to methods (currently not
#'   used by standard methods)
#' @param val_sep a string to be put between SQL values in addition to ","
#'
#' @return an object of type SQL
#'
#' @export
#'
#' @examples
#'
#' #' # SQL VALUES list from vector
#' sqlValues(ANSI(), letters)
#' sqlValues(ANSI(), 4L:7L)
#' sqlValues(ANSI(), 1.3:7.1)
#' sqlValues(ANSI(), rep(Sys.time(), 10))
#'
#' # SQL VALUES list from data.frame like
#' sqlValues(ANSI(), data.frame(letters, seq_along(letters), Sys.time()))
#'
#'
setGeneric(
  name = "sqlValues",
  def  = function(conn, x, ..., val_sep = "\n") standardGeneric("sqlValues")
)


#' @rdname hidden_aliases
#' @export
setMethod(
  f          = "sqlValues",
  signature  = signature("DBIConnection", "vector"),
  definition =
    function(conn, x, ..., val_sep = "\n") {
      DBI::SQL(
        paste0(
          "(",
          DBI::dbQuoteLiteral(conn = conn, x = x),
          ")",
          collapse = paste0(", ", val_sep)
        )
      )
    }
)


#' @rdname hidden_aliases
#' @export
setMethod(
  f          = "sqlValues",
  signature  = signature("DBIConnection", "data.frame"),
  definition =
    function(conn, x, ..., val_sep = "\n") {

      # no data, no sql values
      stopifnot( nrow(x) > 0 )

      # prepare basic call
      sql_values_call <- call("paste0", "(")

      # extend call for each column
      for ( xcol in seq_len(ncol(x)) ) {
        if ( xcol == 1 ){
          # do nothing
        } else {
          # add colon
          sql_values_call[[length(sql_values_call) + 1]] <- ", "
        }

        # add excaped column
        sql_values_call[[length(sql_values_call) + 1]] <-
          DBI::dbQuoteLiteral(conn = conn, x = x[[xcol]])
      }

      sql_values_call[[length(sql_values_call) + 1]] <- ")"

      # run function call
      value_items <- eval(sql_values_call)

      # collapse into single string and return
      DBI::SQL(
        paste0(
          value_items,
          collapse = paste0(", ", val_sep)
        )
      )
    }
)

[krlmlr]

Thanks. I agree this may be useful, but I feel it's outside the scope of DBI. Perhaps this code could live in dbx or another package?