Executing other queries inside user-defined functions

[Prinzhorn]

Essentially #203, but inside user-defined functions. I didn't wanted to hijack that thread as it is moving in a different direction (mutating stuff).

Assuming SQLite allows that, would it be possible to enable SELECT inside user-defined functions? Currently I'm getting "TypeError: This database connection is busy executing a query".

My current use-case is a custom caching mechanism. Passing large BLOB or TEXT to a user-defined function is slow (not necessarily the passing, but reading them from disk) and makes the difference between 5000ms and 30ms. If I could SELECT inside the user-defined function I could only process the data I need and cache the results of the function for later. The user-defined function would then only accept the id as parameter instead of the data itself.

[Prinzhorn]

For the time being I'm using a second readonly connection to query the data inside the user-defined function. Works as expected and is only a little awkward. So if there is a way to flick a switch and allow the same connection to do that, that would be neato.

[JoshuaWise]

For the same reason as in #203, it's currently disabled. But in a future version there might be an "expert mode" that can be turned on, for people with these needs.

[Prinzhorn]

Thanks for the reply.

For the same reason as in #203, it's currently disabled.

Reading is not disabled anymore, #203 is open because the writing part is still open. But I understand your argument and for now I can workaround it. I'm not sure if SELECT inside user-defined functions can have side-effects I'm not aware of. Anyway, I'm probably one of very few people with that use-case, so add it whenever you feel like it. Be it with or without an expert-mode (which might only be needed for writes, not reads).

[Prinzhorn]

For future reference: I don't think SQLite itself has any issues with doing what is asked here

An application-defined function is permitted to call other SQLite interfaces. However, such calls must not close the database connection nor finalize or reset the prepared statement in which the function is running.

https://www.sqlite.org/c3ref/create_function.html

I'm not familiar with SQLite internals, but that sounds like the list of things you can not do is pretty short?