SQL database systems support user-defined functions (UDFs), but they hardly encourage programming with these functions. Quite the contrary: the systems’ focus on plan-based query evaluation penalizes every function call at runtime, rendering programming with UDFs—especially if these are recursive—largely impractical. We propose to take UDFs for what they are (namely functions) and subject UDFs to a pipeline of function compilation techniques well-established by the FP community (CPS conversion, defunctionalization, and translation into trampolined style, in particular). The result is a non-invasive SQL-level compiler for recursive UDFs that naturally supports memoization and emits iterative CTEs which contemporary SQL engines evaluate efficiently. Functions may not be first class in SQL, but functional programming close to the data can still be efficient.
Preprint (authors' version, complete, no pay wall)
GitHub repository with sample recursive SQL UDFs and their compiled plain SQL variants (ready to run on PostgreSQL v11+)
The above preprint has not undergone any post-submission improvements or corrections. The Version of Record of this contribution is published in PADL 2022 Proceedings, and is available online at https://doi.org/10.1007/978-3-030-94479-7_5.