SQL user function returning list for IN clause

[Peter Otten]

Felix wrote:

> 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?

Define a function foobar_contains() as follows:

def foobar_contains(foo, bar):
    return foo in foobar(bar)

and change the query to

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

Peter