On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden <[email protected]> wrote: > Right you are, except it's not a question of efficient but of > possible. A UDF works one of two ways: > > 1. Per row. The engine calls the function for each row. The function > transforms the input into the output. Examples are sqrt() and pow(). > > 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. >> The engine would need to become window-aware, which is >> probably enough work to delay its introduction until a consortium >> member decides they need it. > > I would say the same. It changes the grammar and almost certainly > intrudes on the GROUP BY implementation. AFAIK it's not on the > sqlite4 agenda. I've often wondered if there's interest in a heavier-weight fork of SQLite*. Clearly there isn't: it'd have been done already. Without support from the consortium (and a fork wouldn't get that support) it couldn't possibly hold a candle to SQLite in terms of robustness. > Adding nonstandard equivalent functionality by extending aggregate > functions might be easier. But the inputs to the OVER clause -- > partition, range, order -- would still be required. For aggregate functions like group_concat() an ORDER clause to apply to its inputs would be wonderful. > I'm surprised there's much interest in using SQLite for fancy > grouping. My hobbyhorse is recursive queries, which IMO are much more > of a nuisance to carry out. +1 re: recursive queries. Once or twice I've resorted to a UNION ALL of LEFT OUTER self-JOINs, each sub-query having more and more self-joins -- this limits recursion depth effectively, but there's going to be a limit anyways. I've also use recursive triggers to good effect (though triggers slow things down plenty). Nico -- _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
