postgres-query {RPostgres} | R Documentation |
Execute a SQL statement on a database connection
Description
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.
Usage
## S4 method for signature 'PqConnection,character'
dbSendQuery(conn, statement, params = NULL, ...)
## S4 method for signature 'PqResult'
dbFetch(res, n = -1, ..., row.names = FALSE)
## S4 method for signature 'PqResult'
dbBind(res, params, ...)
## S4 method for signature 'PqResult'
dbHasCompleted(res, ...)
## S4 method for signature 'PqResult'
dbClearResult(res, ...)
Arguments
conn |
A PqConnection created by |
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. |
... |
Other arguments needed for compatibility with generic (currently ignored). |
res |
Code a PqResult produced by
|
n |
Number of rows to return. If less than zero returns all rows. |
row.names |
Either If A string is equivalent to For backward compatibility, |
Examples
# For running the examples on systems without PostgreSQL connection:
run <- postgresHasDefault()
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)