How to use dynamic values while executing SQL scripts in R

Question

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

Masterful

Answer 1

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

Storer

Answer 2

In ?DBI::SQL, you can read:

By default, any user supplied input to a query should be escaped using either dbQuoteIdentifier() or dbQuoteString() depending on whether it refers to a table or variable name, or is a literal string.

Also, on this page:

You may also need dbQuoteIdentifier() if you are creating tables or relying on user input to choose which column to filter on.

So you can use:

sqlInterpolate(ANSI(), 
               "SELECT count(*) FROM ?my_table", 
               my_table = dbQuoteIdentifier(ANSI(), "table_name"))
# <SQL> SELECT count(*) FROM "table_name"

Septuagesima