Execute a SQL statement on a database connection

To retrieve results a chunk at a time, use dbSendQuery(), dbFetch(), then dbClearResult(). Alternatively, if you want all the results (and they'll fit in memory) use dbGetQuery() which sends, fetches and clears for you.

# S4 method for PqConnection,character
dbSendQuery(conn, statement, params = NULL, ...)

# S4 method for PqResult
dbFetch(res, n = -1, ..., row.names = FALSE)

# S4 method for PqResult
dbBind(res, params, ...)

# S4 method for PqResult
dbHasCompleted(res, ...)

# S4 method for PqResult
dbClearResult(res, ...)

Arguments

conn	A PqConnection created by dbConnect().
statement	An SQL string to execute
params	A list of query parameters to be substituted into a parameterised query. Query parameters are sent as strings, and the correct type is imputed by PostgreSQL. If this fails, you can manually cast the parameter with e.g. "$1::bigint".
...	Another arguments needed for compatibility with generic ( currently ignored).
res	Code a PqResult produced by DBI::dbSendQuery().
n	Number of rows to return. If less than zero returns all rows.
row.names	Either TRUE, FALSE, NA or a string. If TRUE, always translate row names to a column called "row_names". If FALSE, never translate row names. If NA, translate rownames only if they're a character vector. A string is equivalent to TRUE, but allows you to override the default name. For backward compatibility, NULL is equivalent to FALSE.

Examples

# For running the examples on systems without PostgreSQL connection:
run <- postgresHasDefault()
#> Could not initialise default postgres database. If postgres is running
#> check that the environment variables PGHOST, PGPORT, 
#> PGUSER, PGPASSWORD, and PGDATABASE, are defined and
#> point to your database.

library(DBI)
if (run) db <- dbConnect(RPostgres::Postgres())
if (run) dbWriteTable(db, "usarrests", datasets::USArrests, temporary = TRUE)

# Run query to get results as dataframe
if (run) dbGetQuery(db, "SELECT * FROM usarrests LIMIT 3")

# Send query to pull requests in batches
if (run) res <- dbSendQuery(db, "SELECT * FROM usarrests")
if (run) dbFetch(res, n = 2)
if (run) dbFetch(res, n = 2)
if (run) dbHasCompleted(res)
if (run) dbClearResult(res)

if (run) dbRemoveTable(db, "usarrests")

if (run) dbDisconnect(db)