How CockroachDB Implements UDFs and SPs
This blog post is co-written by Drew Kimball, Member of Technical Staff, Rachael Harding, Member of Technical Staff, and Dingding Lu, Former Staff Product Manager.
User-defined functions (UDFs) and stored procedures (SPs) allow users to define a set of queries that can be executed at the database by providing only the function or procedure name and any relevant parameters. They provide building blocks for users to compose complex queries, which makes applications less error prone since users don’t need to repeatedly provide the complex query. Another benefit of UDFs/SPs is that they enable better performance by moving more computation closer to the data. There are fewer round trips between the application and database to get the results the user wants.
CockroachDB strives for compatibility with PostgreSQL, and supporting UDFs and SPs has been an interesting technical challenge for our team. So far, CockroachDB supports UDFs and SPs in the SQL and PLpg/SQL languages. PLpg/SQL is a powerful SQL procedural language that adds control structures on top of SQL, allowing even more complex computation such as loops, conditional statements, and exceptions.
