Search code examples
rrpostgresqlr-dbi

How to use dynamic values while executing SQL scripts in R

My R workflow now involves dealing with a lot of queries (RPostgreSQL library). I really want to make code easy to maintain and manage in the future.

I started loading large queries from separate .SQL files (this helped) and it worked great.

Then I started using interpolated values (that helped) which means that I can write

SELECT * FROM table WHERE value = ?my_value;

and (after loading it into R) interpolate it using sqlInterpolate(ANSI(), query, value = "stackoverflow").

What happens now is I want to use something like this

SELECT count(*) FROM ?my_table;

but how can I make it work? sqlInterpolate() only interpolates safely by default. Is there a workaround?

Thanks

Solution

  • sqlInterpolate() is for substituting values only, not other components like table names. You could use other templating frameworks such as brew or whisker.