R: Create SQL 'SELECT' and 'UPDATE' statements

sql {pkgutils}

R Documentation

Create SQL `SELECT` and `UPDATE` statements

Description

Helper function converting R code into SQL SELECT statements and data frames into UPDATE statements.

Usage

  sql(x, ...)

  ## S3 method for class 'data.frame'
 sql(x, where, table,
    set = setdiff(colnames(x), where), ...)

  ## S3 method for class 'formula'
 sql(x, ...)

Arguments

`x`	Data frame or formula.
`where`	Character vector giving the name of the data frame and database table columns used to select rows.
`table`	Character scalar indicating the name of the database table to be updated.
`set`	Character vector giving the name of the data frame and database table columns to be updated.
`...`	Optional arguments passed between methods.

Details

The formula method saves some typing, particularly in the case of complex queries, but it does not support joins. R operators are mostly directly translated except for those with the highest precedence. Infix operators are translated literally. The control structures if and function yield CASE constructs.

To use the data frame method to update a column, say, "x" that is also used to select rows, include "x" in the where argument and "new.x" in the update argument.

Value

Character vector.

Examples

## formula method
x <- mytable(a, b, if (c1 > 15 | c2 == NULL) c1 else c2) ~
  b < 69 & a %in% {"x"
    "y"}
(y <- sql(x))

## [1] "SELECT a, b, CASE WHEN c1 > 15 OR c2 IS NULL THEN c1 ELSE c2 END FROM mytable WHERE b < 69 AND a IN ('x', 'y');"

stopifnot(is.character(y), length(y) == 1L)

[Package pkgutils version 0.7.63 Index]

Create SQL SELECT and UPDATE statements