Package ‘RPostgreSQL’

1 Package rpostgresql June 24, Interface to the ' postgresql ' Database SystemAuthorJoe Conway, Dirk Eddelbuettel, Tomoaki Nishiyama, Sameer Kumar Prayaga (dur-ing 2008), Neil TiffinMaintainerTomoaki interface and ' postgresql ' driver for 'R'.This Package provides a Database Interface 'DBI' compliantdriver for 'R' to access ' postgresql ' database order to build and install this Package from source, ' postgresql 'itself must be present your system to provide ' postgresql ' functionalityvia its libraries and header files. These files are provided as' postgresql -devel' Package under some Linux 'macOS' and 'Microsoft Windows' system the attached 'libpq' library source will be (>= ), methods, DBI (>= )LicenseGPL-2 | file LICENSEC opyrightAuthors listed above, postgresql Global Development Group,and The Regents of the University of , , 07:17:52 UTC12dbApplyRtopics documented:dbApply ..2dbApply-methods ..3dbCallProc-methods ..4dbCommit-methods ..4dbConnect-methods.

2 5dbDataType-methods ..7dbDriver-methods ..7dbGetInfo-methods ..9dbListTables-methods .. 10dbObjectId-class .. 11dbReadTable-methods .. 12dbSendQuery-methods .. 13dbSetDataMappings-methods .. 14fetch-methods .. 15isPostgresqlIdCurrent .. 17 postgresql .. 18postgresqlBuildTableDefinition .. 20 PostgreSQLC onnection-class .. 21postgresqlDBApply .. 22 PostgreSQLD river-class .. 24 PostgreSQLO bject-class .. 25 PostgreSQLR esult-class .. 26postgresqlSupport .. 27summary-methods .. 31 Index33dbApplyApply R/S-Plus functions to remote groups of DBMS rows (experimen-tal)DescriptionApplies R/S-Plus functions to groups of remote DBMS rows without bringing an entire result setall at once. The result set is expected to be sorted by the grouping generic is meant to handle somewhat gracefully(?) large amounts of data from theDBMS by bringing into R manageable chunks; the idea is that the data from individual groups canbe handled by R, but not all the groups at the same , only thePostgreSQLdriver implements a method (see the helper functionpostgresqlDBApply)for this generic list with as many elements as there were groups in the result AlsoPostgreSQL postgresqlDBApply dbSendQuery fetchExamples## Not run:## compute quantiles for each network agentcon <- dbConnect( postgresql (), user= "user", password="passwd", dbname="sample")rs <- dbSendQuery(con,"select Agent, ip_addr, DATA from pseudo_data order by Agent")out <- dbApply(rs, INDEX = "Agent",FUN = function(x, grp) quantile(x$DATA, names=FALSE))## End(Not run)dbApply-methodsApply R/S-Plus functions to remote groups of DBMS rows (experimen-tal)DescriptionApplies R/S-Plus functions to groups of remote DBMS rows without bringing an entire result setall at once.

3 The result set is expected to be sorted by the grouping postgresql result set (seedbSendQuery)..any additional arguments to be passed the Database Interface definition the base directory of this Package AlsoPostgreSQL postgresqlDBApply dbSendQuery fetch4dbCommit-methodsExamples## Not run:## compute quanitiles for each network agentcon <- dbConnect( postgresql (), user="user", password="passwd",dbname="dbname")rs <- dbSendQuery(con,"select Agent, ip_addr, DATA from pseudo_data order by Agent")out <- dbApply(rs, INDEX = "Agent",FUN = function(x, grp) quantile(x$DATA, names=FALSE))## End(Not run)dbCallProc-methodsCall an SQL stored procedureDescriptionNot yet ..additional arguments are passed to the implementing the Database Interface definition the base directory of this Package AlsoPostgreSQL,dbConnect,dbSendQuery,dbG etQuery,fetch,dbCommit,dbGetInfo, Transaction ManagementDescriptionTransaction related commands. Start a transaction, commit or roll back the current transaction inan postgresql a androllback the transaction, , as produced by the.

4 Currently the Database Interface definition the base directory of this Package AlsoPostgreSQL,dbBegin,dbConnect,dbSendQ uery,dbGetQuery,fetch,dbCommit,dbGetInfo , ## Not run:drv <- dbDriver(" postgresql ")con <- dbConnect(drv, dbname="postgres")dbGetQuery(con, "select count(*) from sales")dbBegin(con)rs <- dbSendQuery(con,"Delete * from sales as p where >10")if(dbGetInfo(rs, what = "rowsAffected") > 250){warning("Rolling back transaction")dbRollback(con)}else{dbComm it(con)}dbGetQuery(con, "select count(*) from sales")dbDisconnect(con)## End(Not run)dbConnect-methodsCreate a connection object to an postgresql DBMSD escriptionThese methods are straight-forward implementations of the corresponding generic object of classPostgreSQLD river, or the character string " postgresql " or as produced or the numeric IPaddress of the host to connect to. If address is specified, it should bein the standard IPv4 address format, , or if your machine supports IPv6, youcan also use those addresses.

5 The default is to connect to localhost by a UNIX domain database name. Defaults to "", which is interpreted as postgresql user name to connect as. Defaults to be the same as the operating system nameof the user running the to be used if the server demands password number to connect to at the server (formerly, this specified where to send server debug output).optionsCommand-line options to be sent to the if the communication of date (time stamp) from postgresql is forced to ISOstyle at EffectsA connection between R/S-Plus and an postgresql server is established. The current implementa-tion supports up to 100 simultaneous the Database Interface definition the base directory of this Package AlsoPostgreSQL,dbConnect,dbSendQuery,dbG etQuery,fetch,dbCommit,dbGetInfo, ## Not run:# create an postgresql instance and create one <- dbDriver(" postgresql ")# open the connection using user, passsword, etc., ascon <- dbConnect(drv, dbname = "postgres")df <- dbGetQuery(con, statement = paste("SELECT itemCode, itemCost, itemProfit","FROM sales","SORT BY itemName"));# Run an SQL statement by creating first a resultSet objectrs <- dbSendQuery(con, statement = paste("SELECT itemCode, itemCost, itemProfit","FROM sales","SORT BY itemName"));# we now fetch records from the resultSet into a <- fetch(rs, n = -1) # extract all rowsdim(df)## End(Not run)dbDataType-methods7dbDataType-method sDetermine the SQL Data Type of an S objectDescriptionThis method is a straight-forward implementation of the corresponding generic , ,PostgreSQLD river,PostgreSQLC onnection, object whose SQL type we want to.

6 Any other parameters that individual methods may the Database Interface definition the base directory of this Package AlsoisSQLK eyword ## Not run:data(quakes)drv <- dbDriver(" postgresql ") <- dbDataType(drv, quakes)## End(Not run)dbDriver-methodsPostgreSQL implementation of the Database Interface (DBI) classesand driversDescriptionPostgreSQL driver initialization and closing8dbDriver-methodsMethodsdrvNamecharacter name of the driver to object that inherits fromPostgreSQLD riveras created integer requesting the maximum number of simultanous connections (may be upto 100). number of records to retrieve per fetch. Default is 500. This may beoverridden in calls tofetchwith then= logical used to force re-loading or recomputing the size of the connectiontable. Default the Database Interface definition the base directory of this Package AlsoPostgreSQL,dbConnect,dbSendQuery,dbGetQuery,fetch,dbCommit,dbGetInfo,dbListTables, ## Not run:# create an postgresql instance and set 10000 of rows per ( rpostgresql )drv <- dbDriver(" postgresql ", )# Connecting to postgresql with the specified parameterscon <- dbConnect(drv,user="usrname",password="passwd",dbname="postgres")# Running the query to obtain the resultsetrs <- dbSendQuery(con, "select * from cities where population > 5000")# fetch records into a dataframe.

7 # n = 50 fetched fifty recordsdf <- fetch(rs, n = 50)# n = -1 fetches all the remaining records availabledf2 <- fetch(rs, n = -1)# Clearing the result setdbClearResult(rs)#This returns a character vector (possibly of zero-length)# table names available on the con (con)## End(Not run)dbGetInfo-methods9dbGetInfo-methodsD atabase interface meta-dataDescriptionThese methods are straight-forward implementations of the corresponding generic object that implements some functionality in the R/S-Plus interface to databases (adriver, a connection or a result set)..currently not being indbColumnInfowas changed. Now it may be TRUE, FALSE, or NA; the column may betotally deleted in future releases;ReferencesSee the Database Interface definition the base directory of this Package AlsoPostgreSQL,dbDriver,dbConnect,dbSend Query,dbGetQuery,fetch,dbCommit,dbGetInf o,dbListTables, ## Not run:drv <- dbDriver(" postgresql ")con <- dbConnect(drv, user= "user", password="password", dbname="sample")dbListTables(con)rs <- dbSendQuery(con, )dbGetStatement(rs)dbHasCompleted(rs)inf o <- dbGetInfo(rs)names(dbGetInfo(drv))# DBIC onnection infonames(dbGetInfo(con))# DBIR esult info10dbListTables-methodsnames(dbGetInf o(rs))## End(Not run)dbListTables-methodsList items from an postgresql DBMS and from objectsDescriptionThese methods are straight-forward implementations of the corresponding generic character string with the table.

8 Currently not the Database Interface definition the base directory of this Package AlsoPostgreSQL,dbGetInfo,dbColumnInfo,db Driver,dbConnect,dbSendQueryExamples## Not run:drv <- dbDriver(" postgresql ")# after working (con in dbListConnections(drv)){dbGetStatement(d bListResults(con))}## End(Not run)dbObjectId-class11dbObjectId-classCl ass dbObjectIdDescriptionA helper (mixin) class to provide external references in an R/S-Plus portable from the ClassA virtual Class: No objects may be created from :Object of class"integer"this is an integer vector holding an opaque reference into a C struct(may or may not be a C pointer, may or may not have length one).Methodscoercesignature(from = "dbObjectId", to = "integer"): ..coercesignature(from = "dbObjectId", to = "numeric"): ..coercesignature(from = "dbObjectId", to = "character"): ..formatsignature(x = "dbObjectId"): ..printsignature(x = "dbObjectId"): ..showsignature(object = "dbObjectId"): ..NoteA cleaner mechanism would use external references, but historically this class has existed mainlyfor R/S-Plus ## Not run:pg <- dbDriver(" postgresql ")con <- dbConnect(pg, "user", "password")is(pg, "dbObjectId") ## Trueis(con, "dbObjectId") ## TrueisPostgresqlIdCurrent(con) ## Trueq("yes")$ RisPostgresqlIdCurrent(con) ## False## End(Not run)12dbReadTable-methodsdbReadTable-met hodsConvenience functions for Importing/Exporting DBMS tablesDescriptionThese functions mimic their R/S-Plus counterpartget,assign,exists,remove, andobjects,except that they generate code that gets remotely executed in a database the case ofdbReadTable; otherwise a logical indicating whether the operationwas connection character string specifying a table (or coercible to ).

9 When thevalueis a character string, it is assumed to be a file name containing the data to beloaded; The implementation is INCOMPLETE and should not be used in current ;in the case ofdbReadTable, this argument can be a string or an index specifying the column inthe DBMS table to be used the output (aNULL,"", or 0 specifiesthat no column should be used the output).In the case ofdbWriteTable, this argument should be a logical specifying whether be output to the output DBMS table; ifTRUE, an extra field whose name will be what-ever the R/S-Plus identifier" "maps to the DBMS ( ).overwritea logical specifying whether to overwrite an existing table or not. Its default logical specifying whether to append to an existing table in the DBMS. Its default a allow or prevent PostgreSQLreserved identifiers to be used as column names. By default it used to import data from a file, you may optionally specifyheader=, , ,sep=,eol=, ,skip=, andquote=. NOT FULLYIMPLEMENTED a logical indicating whether the first data line (but seeskip) has a header or not.

10 Ifmissing, it value is determined , namely, it is set to TRUEif and only if the first row has one fewer field that the number of a logical to specify whether the first column is a set of row names. If missingits default follows character vector with column names; column names are quoted to work as SQLidentifiers. Thus, the column names are case sensitive NOT be the field separator, and its default is','.eol=specifies the end-of-line delimiter, and its default is'\n'.skipspecifies number of lines to skip before reading the data, and it defaults to a list of named field SQL types wherenames( )provide the newtable s column names (if missing, field types are inferred usingdbDataType).NotedbWriteTable creates additional column in the database, while dbReadTable reads that column bydefault. So, it is not symmetrical in its current implementation. the backend raw_names may changein future the Database Interface definition the base directory of this Package AlsoPostgreSQL,isSQLK eyword,dbDriver,dbConnect,dbSendQuery,db GetQuery,fetch,dbCommit,dbGetInfo,dbList Tables, ## Not run:conn <- dbConnect(" postgresql ", dbname = "wireless")if(dbExistsTable(con, "frame_fuel")){dbRemoveTable(conn, "frame_fuel")dbWriteTable(conn, "frame_fuel", )}if(dbExistsTable(conn, "RESULTS")){dbWriteTable(conn, "RESULTS", results2000, append = T)elsedbWriteTable(conn, "RESULTS", results2000)}## End(Not run)dbSendQuery-methodsExecute a statement on a given database connectionDescriptionThese methods are straight-forward implementations of the corresponding generic functions.