RPostgres: postgres-query – R documentation

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

postgres-query

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 `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"`.
`...`	Other 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()

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)

RPostgres

'Rcpp' Interface to 'PostgreSQL'

v1.3.2

GPL-3

Authors

Hadley Wickham [aut], Jeroen Ooms [aut], Kirill Müller [aut, cre] (<https://orcid.org/0000-0002-1416-3412>), RStudio [cph], R Consortium [fnd], Tomoaki Nishiyama [ctb] (Code for encoding vectors into strings derived from RPostgreSQL)

Initial release

2021-04-12