Insert R list into RPostgreSQL query

Question

I'm running a postgreSQL query based on an automated list of ID's stored in an R list. I'm trying to determine how to include that R list in my query so I don't have to hard-code the ID's each time I run my query.

For example, I have a script that produces the list

id <- c("001","002","003")

and my query looks something like this:

SELECT *
FROM my_query
WHERE my_query.id_col IN ('001', '002', '003')

which I run using Rpostgres:

library(Rpostgres)
snappConnection <- DBI::dbConnect(RPostgres::Postgres(),
                                  host = "host",
                                  dbname = "dbname",
                                  user = "user",
                                  password = "pword",
                                  port = 0000)
core.data <- dbGetQuery(conn = snappConnection,statement = SELECT * FROM my_query WHERE my_query.id_col IN ('001', '002', '003'))

Is there a way to reference my "id" list from R in my query so that when "id" updates to new values, the query also updates to those new values?

score 1 · Accepted Answer · answered Feb 14 '22 at 20:34

1

glue_sql from glue package should work:

query <- glue::glue_sql("
SELECT *
FROM my_query
WHERE my_query.id_col IN ({id*})              
", .con = snappConnection)

core.data <- dbGetQuery(conn = snappConnection, statement = query)

answered Feb 14 '22 at 20:34

dave-edison

3,666
7
19

So forgive my poor sql background; my query is approximately 500 lines long and contains multple quotation marks, which, in this example, buggers things up. I tried saving the ({id*}) in my script and then `script <- read_file("my_query.sql") query <- glue::glue_sql(script, .con = snappConnection)` but had no luck. Any idea how to adjust such that my current script's quotation marks don't interfere? – spencergadd Feb 14 '22 at 21:12
I guess I should be more specific. After saving the script as a string with read_file(), and running `test.query <- glue::glue_sql(test.script, .con = snappConnection) core.data <- dbGetQuery(conn = snappConnection, statement = test.query)`, I get the Error: Expecting a single string value: [type=character; extent=6]. I currently have 6 elements in my id list, fyi. – spencergadd Feb 14 '22 at 21:24
Nope, I just had a typo. It worked! Thank you. – spencergadd Feb 14 '22 at 21:53

score 0 · Answer 2 · answered Feb 14 '22 at 22:00

@dave-edison's answer solved my problem. Concurrent to trying his, I got this to work.

I saved the query below as "my_query.sql"

SELECT *
FROM my_query
WHERE my_query.id_col IN ('string_to_replace')

then created a string and used gsub on the string.

library(tidyverse)
temp.script <-  read_file("my_query.sql")
core.data.script <- gsub('string_to_replace',paste0(id,collapse = "', '"),temp.script)

From there I just ran my RPostgres script like above.

Insert R list into RPostgreSQL query

2 Answers2