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 -- http://mail.python.org/mailman/listinfo/python-list