Add a function with multiple return values to SQLite in Python

Question

I want to add a function to SQLite in Python (like explained here)

My function returns multiple values. In python, I can access to different return values by indexing (using []).

However, it seems indexing does not work in SQLite. In other words, the following SELECT statement will have an error:

SELECT my_function(table1.column1)[0] FROM table1;

sqlite3.OperationalError: user-defined function raised exception

Is there any way to access to different return values in SQLite?

Answer 1

The only way to return multiple values from a function is with a table-valued function, which requires creating a virtual table module, which is not possible with only the default Python SQLite driver.

There are additional Python modules to allow this, for example, sqlite-vtfunc.

Answer 2

One way to do that is to have the function return a string with the multiple values encoded as a JSON array and then use SQLite JSON extraction functions to access the individual values:

select json_extract(fout, $[0]) as v0,
       json_extract(fout, $[1]) as v1
from (select my_func(cols...) as fout
      from ...)

I have used that solution myself and performance is ok. The JSON encoding/decoding doesn't seem to introduce a noticeable performance penalty.