rpostgresqlterr

Execute Postgres Function in R

I am trying to execute a postgres function within R (using Spotfire Terr), however I cant get the syntax to work for dbSendQuery

I have tried both dbGetQuery and dbSendQuery and can't get it to work. I looked for several examples, but the only ones that I have found are those that have "Insert into" or "UPDATE" statements. I haven't been able to find how to execute a function. I saw that it is possible with sql server using sqlExecute (dbcon, "EXEC ....."). Is there something equivalent for Postgres?

so the function does a few insert statements based on 3 parameters that are passed. Here's what I tried:

Name<-'this is a test'
AInteger<-1
BInteger<-2
result<- dbSendQuery(conn, 'select * from "Main"."InsertDataSet"(?,?,?)', list(Name, AInteger,bInteger))

the function takes 3 parameters. In postgres database, I call the function with select * from "Main"."InsertDataSet"('a name',123,124). I figured that it could be called the same way in R...I can't find a diff example, that is otherwise.

I get the following error

TIBCO Spotfire Statistics Services returned an error: 'Error: RS-DBI driver: (could not Retrieve the result : ERROR: syntax error at or near "?" LINE 1: select * from "Main"."InsertDataSet"? ^ ) eval(expr, envir, enclos) eval(expr, envir, enclos) dbSendQuery(conn, "select * from \"Main\".\"InsertDataSet\"?", standardGeneric("dbSendQuery") .standardGeneric("dbSendQuery", structure(function(conn, statement, dbSendQuery(conn, "select * from \"Main\".\"InsertDataSet\"?", postgresqlExecStatement(conn, statement, ...) NULL'. at Spotfire.Dxp.Data.DataFunctions.Executors.RemoteFunctionClient.OnExecuting() at Spotfire.Dxp.Data.DataFunctions.Executors.AbstractFunctionClient.d__31.MoveNext() at Spotfire.Dxp.Data.DataFunctions.Executors.SPlusFunctionExecutor.d__12.MoveNext() at Spotfire.Dxp.Data.DataFunctions.DataFunctionExecutorService.d__3.MoveNext()

Solution

  • I have never used the R Postgres package, but going by this SO answer I can suggest the following:

    sql <- "SELECT * FROM Main.InsertDataSet($1,$2,$3)"
result <- dbSendQuery(conn, sql, c(Name, AInteger,bInteger))
out <- dbFetch(result)
dbClearResult(result)

    or more directly

    out <- dbGetQuery(conn, sql, c(Name, AInteger,bInteger))