search

xerial / sqlite-jdbc

SQLite JDBC Driver
Apache License 2.0
2.83k stars 619 forks source link

Support for table-valued functions #216

Open okennedy opened 7 years ago

okennedy commented 7 years ago

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 commented 7 years ago

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 commented 7 years ago

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 commented 7 years ago

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 commented 4 years ago

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.