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?

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

Answer 2

@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.