Support for table-valued functions

[okennedy]

First off, thanks for a great piece of software.

SQLite now supports Table-Valued functions. Is there any possibility of the JDBC driver getting an interface for these in the same vein as Function or Function.Aggregate.

[gwenn]

A table-valued function is used like a function but is implemented by a virtual table. So the driver must support at least read-only virtual table (sqlite3_module, sqlite3_vtab and sqlite3_vtab_cursor).

[okennedy]

Thanks!

A virtual table would be sufficient for my purposes as well. It seems like the jdbc driver can make use of vtables, but only those defined by an extension.

[gwenn]

Indeed, the driver can load/use C extension containing virtual table implementation(s) but you can't implement virtual table in Java. If you want to use an existing table-valued function, you can already do that with: http://sqlite.org/lang_corefunc.html#load_extension

SELECT load_extension('path to the dll');
SELECT value FROM table_valued_func();

[aaime]

Agreed this would be pretty useful. I have a case where a query needs to extract the bounds of a polygon, minx,miny,maxx,maxy, for r-tree insertion, and it's round tripping 4 times back to Java to call 4 separate functions, where one round trip would be sufficient... but to do that, I understand table valued functions would be required.