RPostgres (version 1.4.0)

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 PqConnection
dbSendQuery(conn, statement, params = NULL, ..., immediate = FALSE)
# 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".

...

Other arguments needed for compatibility with generic (currently ignored).

immediate

If TRUE, uses the PGsendQuery() API instead of PGprepare(). This allows to pass multiple statements and turns off the ability to pass parameters.

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.

Multiple queries and statements

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.

Examples

# NOT RUN {
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")

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

dbRemoveTable(db, "usarrests")

dbDisconnect(db)
# }