Execute Postgres Function in R

Question

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()

r2evans · Accepted Answer · 2019-08-27 16:40:33Z

0

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))

edited Aug 27, 2019 at 16:40

r2evans

167k8 gold badges92 silver badges176 bronze badges

answered Aug 27, 2019 at 16:13

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

yanci Over a year ago

i made the change above,but now i am getting an error with my last statement dbDisconnect(conn) Error in postgresqlCloseConnection(conn, ...) : connection has pending rows (close open results set first) Am I suppose to send the results somewhere before closing the connection?

Tim Biegeleisen Over a year ago

OK what is this Postgres function doing?

yanci Over a year ago

I used dbGetQuery instead of dbSendQuery and it now works without any errors. Thanks!!

r2evans Over a year ago

@TimBiegeleisen, I assumed a little by editing your question, hope I didn't go too far.

Tim Biegeleisen Over a year ago

@r2evans Not at all, I appreciate your help. The OP's original attempt to bind a list seemed off to me, so I tried to dig around.

Collectives™ on Stack Overflow

Execute Postgres Function in R

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related