R/PqResult.R
, R/dbBind_PqResult.R
, R/dbClearResult_PqResult.R
, and 3 more
postgres-query.Rd
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 PqResult
dbBind(res, params, ...)
# S4 method for PqResult
dbClearResult(res, ...)
# S4 method for PqResult
dbFetch(res, n = -1, ..., row.names = FALSE)
# S4 method for PqResult
dbHasCompleted(res, ...)
# S4 method for PqConnection
dbSendQuery(conn, statement, params = NULL, ..., immediate = FALSE)
Code a PqResult produced by
DBI::dbSendQuery()
.
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"
.
Other arguments needed for compatibility with generic (currently ignored).
Number of rows to return. If less than zero returns all rows.
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
.
A PqConnection created by dbConnect()
.
An SQL string to execute.
If TRUE
, uses the PGsendQuery()
API instead of PGprepare()
.
This allows to pass multiple statements and turns off the ability to pass parameters.
With immediate = TRUE
, it is possible to pass multiple queries or statements,
separated by semicolons.
For multiple statements, the resulting value of dbGetRowsAffected()
corresponds to the total number of affected rows.
If multiple queries are used, all queries must return data with the same
column names and types.
Queries and statements can be mixed.
library(DBI)
db <- dbConnect(RPostgres::Postgres())
dbWriteTable(db, "usarrests", datasets::USArrests, temporary = TRUE)
# Run query to get results as dataframe
dbGetQuery(db, "SELECT * FROM usarrests LIMIT 3")
#> Murder Assault UrbanPop Rape
#> 1 13.2 236 58 21.2
#> 2 10.0 263 48 44.5
#> 3 8.1 294 80 31.0
# Send query to pull requests in batches
res <- dbSendQuery(db, "SELECT * FROM usarrests")
dbFetch(res, n = 2)
#> Murder Assault UrbanPop Rape
#> 1 13.2 236 58 21.2
#> 2 10.0 263 48 44.5
dbFetch(res, n = 2)
#> Murder Assault UrbanPop Rape
#> 1 8.1 294 80 31.0
#> 2 8.8 190 50 19.5
dbHasCompleted(res)
#> [1] FALSE
dbClearResult(res)
dbRemoveTable(db, "usarrests")
dbDisconnect(db)