PostgreSQL query

Share:

Description

Issue a query to the current database connection

Usage

1
2
3
4
5
6
7
8
9
result_dim()

query(sql = "", pars = NULL)

query_error()

fetch(sql = "", pars = NULL)

execute(...)

Arguments

sql

a query string

pars

a character vector of substitution values

...

list of commands to be pasted together

Details

fetch returns the result of a query as a data frame. If sql is NULL or empty, then an attempt will be made to retrieve any pending resutls from previous queries. Note that query results are not cleared until the next query is issued so fetch will continue to return results until a new query is issued.

execute is a wrapper around query. It will raise an exception if the command does not complete. Exceptions can be caught with tryCatch. You cannot use a parameterized query with execute. Unlike query it will paste its arguments into a single string.

Value

result_dim returns the number of tuples and fields

query returns:

PGRES_EMPTY_QUERY The string sent to the server was empty
PGRES_COMMAND_OK Successful completion of a command returning no data
PGRES_TUPLES_OK Successful completion of a command returning data (such as a SELECT or SHOW)
PGRES_COPY_OUT Copy Out (from server) data transfer started
PGRES_COPY_IN Copy In (to server) data transfer started
PGRES_BAD_RESPONSE The server's response was not understood.
PGRES_NONFATAL_ERROR A nonfatal error (a notice or warning) occurred
PGRES_FATAL_ERROR A fatal error occurred
PGRES_COPY_BOTH Copy In/Out (to and from server) data transfer started. This is currently used only for streaming replication

query_error returns an error string

fetch returns a data frame or a query status object on failure.

execute the result status string

Author(s)

Timothy H. Keitt

See Also

psql

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
## Not run: 
system("createdb rpgtesting")
connect("rpgtesting")
begin()
execute("DROP SCHEMA IF EXISTS rpgtesting CASCADE")
execute("CREATE SCHEMA rpgtesting")
execute("SET search_path TO rpgtesting")
execute("DROP TABLE IF EXISTS test")
execute("CREATE TABLE test (id integer, field text)")
query("INSERT INTO test VALUES ($1, $2)", c(1, "test"))
fetch("SELECT * FROM test")
result_dim()
fetch("SELECT * FROM testing")
query_error()
rollback()
disconnect()
system("dropdb rpgtesting")
## End(Not run)