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?
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:
I have used that solution myself and performance is ok. The JSON encoding/decoding doesn't seem to introduce a noticeable performance penalty.