pass a column name correctly as a function argument for SQL query in R

Question

I have a datafile sales_history. I want to query it in the following way.

my_df<-sqldf("SELECT * 
                    FROM sales_history
                    WHERE Business_Unit=='RETAIL'"")

Now I want to write a function with argument datafile and column name to do the above job. So something like:

pick_column<-function(df, column_name){
  my_df<-sqldf("SELECT * 
                      FROM df
                      WHERE Business_Unit==column_name"
  return(my_df)
}

Ideally, after running the above function definition, I should then be able to run pick_column(sales_history,'RETAIL'). But when I do this, the second argument 'RETAIL' is not passed to the function correctly. What's the correct way to do this then?

I know that for this example, there are other ways to do this other than using "sqldf" for SQL query. But the point of my question here is how to pass the column_name correctly as a function argument.

Answer 1

the sqldf package uses gsubfn to allow you to add names of R variables into your SQL commands by prefixing them with the "$" character. So you can write

sales_history <- data.frame(
    price=c(12,10),
    Business_Unit=c("RETAIL","BUSINESS"),
    stringsAsFactors=F
)

pick_column <- function(df, columnname) {
    fn$sqldf("SELECT * FROM $df WHERE Business_Unit='$columnname'")
}

pick_column("sales_history","RETAIL")