ifelse & grepl commands when using dplyr for SQL in-db operations

Question

In dplyr running on R data frames, it is easy to run

df <- df %>% 
    mutate(income_topcoded = ifelse(income > topcode, income, topcode)

I'm now working with a large SQL database, using dplyr to send commands to the SQL server. When I run the same command, I get back

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  
function ifelse  (boolean, numeric, numeric) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

How would you suggest implementing ifelse() statements? I'd be fine with something in PivotalR (which seems to support ifelse(), but I don't know how to integrate it with dplyr and couldn't find any examples on SO), some piece of SQL syntax which I can use in-line here, or some feature of dplyr which I was unaware of.

(I have the same problem that I'd like to use grepl() as an in-db operation, but I don't know how to do so.)

Answer 1

Based on @hadley's reply on this thread, you can use an SQL-style if() statement inside mutate() on dplyr's in-db dataframes:

df <- df %>% 
    mutate( income_topcoded = if (income > topcode) income else topcode)

As far as using grepl() goes...well, you can't. But you can use the SQL like operator:

df  <- df %>%
    filter( topcode %like% "ABC%" )