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