render Renders SQL code based on parameterized SQL and parameter values.
render(sql, warnOnMissingParameters = TRUE, ...)Arguments
- sql
The parameterized SQL
- warnOnMissingParameters
Should a warning be raised when parameters provided to this function do not appear in the parameterized SQL that is being rendered? By default, this is TRUE.
- ...
Parameter values
Value
A character string containing the rendered SQL.
Details
This function takes parameterized SQL and a list of parameter values and renders the SQL that can be send to the server. Parameterization syntax:
- @parameterName
Parameters are indicated using a @ prefix, and are replaced with the actual values provided in the render call.
- {DEFAULT @parameterName = parameterValue}
Default values for parameters can be defined using curly and the DEFAULT keyword.
- {if}?{then}:{else}
The if-then-else pattern is used to turn on or off blocks of SQL code.
Examples
render("SELECT * FROM @a;", a = "myTable")
#> [1] "SELECT * FROM myTable;"
render("SELECT * FROM @a {@b}?{WHERE x = 1};", a = "myTable", b = "true")
#> [1] "SELECT * FROM myTable WHERE x = 1;"
render("SELECT * FROM @a {@b == ''}?{WHERE x = 1}:{ORDER BY x};", a = "myTable", b = "true")
#> [1] "SELECT * FROM myTable ORDER BY x;"
render("SELECT * FROM @a {@b != ''}?{WHERE @b = 1};", a = "myTable", b = "y")
#> [1] "SELECT * FROM myTable WHERE y = 1;"
render("SELECT * FROM @a {1 IN (@c)}?{WHERE @b = 1};",
a = "myTable",
b = "y",
c = c(1, 2, 3, 4)
)
#> [1] "SELECT * FROM myTable WHERE y = 1;"
render("{DEFAULT @b = \"someField\"}SELECT * FROM @a {@b != ''}?{WHERE @b = 1};",
a = "myTable"
)
#> [1] "SELECT * FROM myTable WHERE someField = 1;"
render("SELECT * FROM @a {@a == 'myTable' & @b != 'x'}?{WHERE @b = 1};",
a = "myTable",
b = "y"
)
#> [1] "SELECT * FROM myTable WHERE y = 1;"
render(
sql = "SELECT * FROM @a;",
warnOnMissingParameters = FALSE,
a = "myTable",
b = "missingParameter"
)
#> [1] "SELECT * FROM myTable;"