Hacker News new | ask | show | jobs
by yomritoyj 1682 days ago
This what is so great and yet limiting about SQL. Carefully optimized implementations, but only for a fixed set of built-in functions. Are there any frameworks which allow user-defined functions to gain some of these optimization benefits without exposing the full internals of the data-store implementation to them?
1 comments
1egg0myegg0 1682 days ago
DuckDB has a few options for custom functions! I'm not sure exactly how optimized they are relative to what you have in mind, but I believe there is still quite a lot of query optimization that you get out of the box.

The first option is to create a macro: https://duckdb.org/docs/sql/statements/create_macro

The second is Python-specific, but you can have UDF's straight from Python! It's on the roadmap to scale this to the other supported languages. https://github.com/duckdb/duckdb/pull/1569

In general, I also think that SQL is still abstracting a lot of the complexity you would have to handle in an imperative language. These holistic moving averages are actually a great example of why I love SQL! I just learn the WINDOW functions syntax once and it works for many queries.

link