On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams <n...@cryptonector.com> wrote: > On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden > > 2. Aggregation. The engine passes the rows to be aggregated to the > > function. After the last row, it calls the function's "xFinal" > > function, which returns a value. Example: median(). > > Lies! :) There's one more type of UDF in SQLite3: virtual tables. > You could have a virtual table where selecting with an equality test > for some column is "passing an argument" to a "table function". I > know, I've done this. But it gets better! SQLite3 is re-entrant, so > you can actually format a SQL statement in the virtual table given its > "arguments" and execute it (being careful to not create a SQL > injection vulnerability). IIRC FTS works this way. > > With a tiny bit of syntactic sugar we could have a way to define table > functions given simple scalar functions, which would be very nice > indeed.
Right. Virtual Tables are very flexible, but the syntax is indeed not practical, and it also forces you to name and "instantiate" tables, when often you want to use table functions on the fly in a JOIN. Tables functions are very useful to "re-tabulate" de-normalized data stuffed into a CSV or JSON or XML text column for example. Once you have table functions, windowing analytics probably becomes easier as well to express (although not necessarily efficient). VTables just isn't the right fit for table functions IMHO. My $0.02. --DD http://www.oracle-base.com/articles/misc/pipelined-table-functions.php PS: Table functions are basically the "reverse" of aggregate functions to me. They take scalar inputs, and generate rows, while aggregate functions take rows and generate scalar(s).. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
