Pass SQL functions in dplyr filter function on database
<p>I'm using <code>dplyr</code>'s automatic SQL backend to query subtable from a database table. E.g.</p>

<pre><code>my_tbl &lt;- tbl(my_db, "my_table")
</code></pre>

<p>where <code>my_table</code> in the database looks like</p>

<pre><code>batch_name    value
batch_A_1     1
batch_A_2     2
batch_A_2     3
batch_B_1     8
batch_B_2     9
...
</code></pre>

<p>I just want the data from <code>batch_A_#</code>, regardless of the number. </p>

<p>If I were writing this in SQL, I could use </p>

<pre><code>select * where batch_name like 'batch_A_%'
</code></pre>

<p>If I were writing this in R, I could use a few ways to get this: <code>grepl()</code>, <code>%in%</code>, or <code>str_detect()</code></p>

<pre><code># option 1
subtable &lt;- my_tbl %&gt;% select(batch_name, value) %&gt;%
    filter(grepl('batch_A_', batch_name, fixed = T))
# option 2
subtable &lt;- my_tbl %&gt;% select(batch_name, value) %&gt;%
    filter(str_detect(batch_name, 'batch_A_'))
</code></pre>

<p>All of these give the following Postgres error: <code>HINT:  No function matches the given name and argument types. You might need to add explicit type casts</code></p>

<p>So, how do I pass in SQL string functions or matching functions to help make the generated dplyr SQL query able to use a more flexible range of functions in <code>filter</code>?</p>

<p>(FYI the <code>%in%</code> function does work, but requires listing out all possible values. This would be okay combined with <code>paste</code> to make a list, but does not work in a more general regex case)</p>