title: PostgreSQL set-returning functions are not allowed in WHERE using JPA
body: <p>I have a sql function that looks like:</p>
  <pre><code>CREATE OR  replace FUNCTION search(IN word character VARYING) 
  returns setof certificates AS $$SELECT c 
  FROM   certificates c 
  WHERE  Upper(description) LIKE Upper('%\'||word||'%') 
  OR Upper(NAME) LIKE Upper('%\' 
                ||word 
                ||'%') $$ language sql;
  </code></pre>
  <p>And I'm trying to call it using JPA like:</p>
  <pre><code>SELECT c FROM Certificates c WHERE c in (function('search',:findPhrase ))
  </code></pre>
  <p>And I'm getting <code>set-returning functions are not allowed in WHERE</code>
  But if I change the return type of my sql function to just certificates instead of setof certificates it works. But it only finds 1 record.
  I was trying different things including casting result to an array and using ids instead of whole objects. Nothing really helps.
  So the question is: How to make my code work as desired?</p>
  <p>I simplified my real case to just show the problematic part. The whole SQL looks more like:</p>
  <pre><code>SELECT c FROM Certificates 
  JOIN c.tags t WHERE t.name IN :names &quot;
  &quot;GROUP BY (c.id) &quot;
  &quot;HAVING COUNT(c.id) &gt;= :size
    AND c IN (function('search',:findPhrase )) 
  </code></pre>
  <p>At the end i switched my TypedQuery to NativeQuery and used this code</p>
  <pre><code>AND c in(select search(:findPhrase))
  </code></pre>
  <p>So the whole SQL now looks like</p>
  <pre><code>SELECT * FROM certificates c JOIN certificate_tags ct ON c.id = ct.certificate_id JOIN tags t ON t.id = ct.tag_id WHERE t.name IN (?) GROUP BY (c.id, ct.id, t.id) HAVING COUNT(c.id) &gt;= ? AND c in(select search(?))
  </code></pre>