prepare: Prepared queries

Description Usage Arguments Details Value Note Author(s) Examples

View source: R/rpg.R

Description

Prepare and execute queries

Usage

1

Arguments

sql

a valid query string

Details

prepare prepares a statement for later execution. It returns a function that when called executes the prepared statement. Values passed to the returned function will substituted for parameters in the prepared statement. If the number of parameters supplied is a multiple of the number of open parameters in query prepared using prepare, then the prepared query will be executed repeatedly for each successive set of parameters. This repeated execution loop is evaluted in C++ and so is quite fast. The supplied parameter values will be coerced to a matrix of the appropriate dimensions. Values passed to the function will be recycled to match the number of query parameters. The passed parameters will be coerced to character strings.

Value

A function.

The function can take one argument. The values will be used to fill in parameters of the prepared statement. If no argument is passed, the statement will be executed without any parameters.

Note

One can use pure SQL to achieve the same result.

It is generally a good idea to wrap prepare in a transaction. If not in a transaction, you cannot rollback any updates and it will be much slower as PostgreSQL initiates a transaction-per-query by default.

Author(s)

Timothy H. Keitt

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
## Not run: 
# try connecting to default database
createdb("rpgtesting")
connect("rpgtesting")
begin()

# write data frame contents
data(mtcars)
write_table(mtcars)

# delete the rows
query("truncate mtcars")
read_table(mtcars)

# use prepare-execute to write rows
pars = paste0("$", 1:11, collapse = ", ")
sql = paste0("INSERT INTO mtcars VALUES (", pars, ")", collapse = " ")
f = prepare(sql)
f(mtcars)
read_table(mtcars, limit = 5)

# cleanup
rollback()
disconnect()
dropdb("rpgtesting")
## End(Not run)

thk686/rpg documentation built on Sept. 5, 2019, 11:13 p.m.