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

Scarabee · Accepted Answer · 2017-08-25 11:28:07Z

3

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"

answered Aug 25, 2017 at 11:28

Scarabee

5,7545 gold badges32 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

krlmlr · Accepted Answer · 2017-04-13 14:22:12Z

0

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

answered Apr 13, 2017 at 14:22

krlmlr

25.6k14 gold badges127 silver badges231 bronze badges

Collectives™ on Stack Overflow

How to use dynamic values while executing SQL scripts in R

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related