SQL user function returning list for IN clause

[Felix]

I am using the Python SQLite3 interface, but the question is probably
general to python and SQL.

I want to run a query like

select * from table a, table b where a.foo IN foobar(b.bar)

where foobar is a user function (registered by create_function in
pysqlite3) returning a list of integers. However such functions can
only return basic data types so the above is invalid. I am wondering
what the best way around this is.

I could fetch rows from table b, compute foobar(b.bar) and create a
new query for each result, but that seems very inefficient.
I could create a new table matching each row in b to all values of
b.bar and use that to join but that would be inefficient and very
redundant.

Rewriting the query to say
select * from table a, table b where foobar_predicate(a.foo, b.bar)
would work (foobar_predicate checks if a.foo is in foobar(b.bar). But
it does not allow to use an index on a.foo

If I knew the maximum length of foobar(b.bar) I could say
select * from table a, table b where a.foo in (foobar(b.bar,0), foobar
(b.bar,1), ..., foobar(b.bar,n))
where the second parameter to foobar chooses which element to return.
This is clearly not optimal.

Am I missing some obvious elegant way to do this or is it just not
possible given that the SQL IN statement does not really deal with
lists in the python sense of the word?

Thanks