call SQL function within R function

Question

I am wondering if it is possible to call an SQL function within an R function?

Say for example that I have this dummy data and SQL function written in Postgres 9.3

CREATE TABLE tbl (
   id VARCHAR(2) PRIMARY KEY
   ,name TEXT
   ,year_born NUMERIC
   ,nationality TEXT
);

INSERT INTO tbl(id, name, year_born, nationality)
VALUES ('A1','Bill',2001,'American')
      ,('B1','Anna',1997,'Swedish')
      ,('A2','Bill',1991,'American')
      ,('B2','Anna',2004,'Swedish')
      ,('B3','Anna',1989,'Swedish')
      ,('A3','Bill',1995,'American');


CREATE FUNCTION retrieve_data(TEXT) 
RETURNS TABLE ( id VARCHAR(2), name TEXT, year_born NUMERIC, nationality TEXT ) AS 
$func$
SELECT id, name, year_born, nationality
FROM tbl
WHERE name=$1 OR nationality=$1
GROUP BY 1
ORDER BY 1
$func$ LANGUAGE sql

I can access this data and run the function within the R environment through the RPostgreSQL and sqldf packages as below;

require(RPostgreSQL)
require(sqldf)

options(sqldf.RPostgreSQL.user = "****", 
        sqldf.RPostgreSQL.password = "****",
        sqldf.RPostgreSQL.dbname = "test_db",
        sqldf.RPostgreSQL.host = "localhost", 
        sqldf.RPostgreSQL.port = 5432)

sqldf("select * from retrieve_data('Bill')")

But is there a way to call the above SQL function within an R function, e.g. like;

myfunc <- function(name) {
sqldf("select * from retrieve_data(name)")
}

myfunc('Bill')

Any pointers would be very much appreciated, thanks!

UPDATE

Using the $fn prefix in the sqldf package as suggested by @G. Grothendieck

myfunc2 <- function(name){
   fn$sqldf("select * from retrieve_data('$name')")
}

Or exchange the above options to the below code to match the suggested answer by @dickoa

require(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,
                 user="****",
                 password="****",
                 dbname="test_db",
                 host="localhost",
                 port=5432)

Try fn$sqldf("select * from retrieve_data($name)") as per example 5 on the sqldf home page: code.google.com/p/sqldf/#Example_5._Insert_Variables — G. Grothendieck
– G. Grothendieck, Commented Jan 23, 2014 at 20:24

dickoa · Accepted Answer · 2014-01-23 19:36:39Z

3

The trick is to use shQuote and sprintf but there are clever way to do that I'm sure.

library(sqldf)
library(RPostgreSQL)

options(sqldf.RPostgreSQL.user = "****",
        sqldf.RPostgreSQL.dbname = "****",
        sqldf.RPostgreSQL.host = "localhost",
        sqldf.RPostgreSQL.port = 5432)

myfunc <- function(name)
    sqldf(sprintf("select * from retrieve_data(%s)", shQuote(name)))

myfunc('Bill')
##   id name year_born nationality
## 1 A1 Bill      2001    American
## 2 A2 Bill      1991    American
## 3 A3 Bill      1995    American

If you want to avoid quoting the string then you can use

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "tempdb")
myfunc2 <- function(name)
    dbGetQuery(con, "select * from retrieve_data($1)", name)

myfunc2("Bill")
##   id name year_born nationality
## 1 A1 Bill      2001    American
## 2 A2 Bill      1991    American
## 3 A3 Bill      1995    American

edited Jan 23, 2014 at 19:36

answered Jan 23, 2014 at 19:23

dickoa

18.5k3 gold badges38 silver badges50 bronze badges

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

2 Comments

Brian Diggs Over a year ago

I think you would want postgresqlEscapeStrings instead of shQuote because you need quoting specific to SQL (and postgresql), not to a command line shell. (Though I'm not sure what you use for the con argument in this case.)

dickoa Over a year ago

@BrianDiggs That's the main problem, I don't know how to find a con type object using sqlf. I edited and proposed a cleaner approach

Collectives™ on Stack Overflow

call SQL function within R function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related