Functional-Style SQL UDFs With a Capital ‘F’
Torsten Grust • Christian Duta
Proceedings of the 39th ACM SIGMOD Int’l Conference on Management of Data (SIGMOD 2020), Portland, Oregon, USA, June 2020. Winner of the ACM SIGMOD 2021 Reproducibility Award.
We advocate to express complex in-database computation using a functional style in which SQL UDFs use plain self-invocation to recurse. The resulting UDFs are concise and readable, but their run time performance on contemporary RDBMSs is sobering. This paper describes how to compile such functional-style UDFs into SQL:1999 recursive common table expressions. We build on function call graphs to build the compiler’s core and to realize a series of optimizations (reference counting, memoization, exploitation of linear and tail recursion). The compiled UDFs evaluate efficiently, challenging the performance of manually tweaked (but often convoluted) SQL code. SQL UDFs can indeed be functional and fast.
This paper has won the ACM SIGMOD 2021 Reproducibility Award.