在R函数中调用SQL函数 [英] call SQL function within R function
问题描述
我想知道是否可以在 R
函数中调用 SQL
函数吗?
I am wondering if it is possible to call an SQL
function within an R
function?
例如,我有这个伪数据和 SQL
函数编写在 Postgres 9.3中
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
我可以访问此数据并在 R中运行该函数通过如下所示的
RPostgreSQL
和 sqldf
软件包的code>环境;
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')")
但是有一种方法可以在 R
f中调用上述 SQL
函数功能,例如像;
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!
更新
使用< @G建议在 sqldf
包中添加code> $ fn 前缀。 Grothendieck
Using the $fn
prefix in the sqldf
package as suggested by @G. Grothendieck
myfunc2 <- function(name){
fn$sqldf("select * from retrieve_data('$name')")
}
或交换上述选项
到以下代码以匹配@dickoa的建议答案
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)
推荐答案
诀窍是使用 shQuote
和 sprintf
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
这篇关于在R函数中调用SQL函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!