Metaprogramming in SQL (Part 1)

Metaprogramming can feel like magic. You call a function that you neither wrote nor imported from any library and, magically, it comes back with a result. Even more magical is how metaprogramming lets you do otherwise impossible things with your programming language. In “The Art of Metaprogramming”, Jonathan Bartlett’s developerWorks series, he lists three examples that illustrate the benefits of metaprogramming:

You can use metaprogramming to pre-generate tables of data for use at run-time.

In applications with large amounts of boilerplate code and limited ability to abstract this code cleanly into functions, you can use metaprogramming to create a mini-language to write the boilerplate for you at run-time, simplifying your own code.

You can use metaprogramming to transform a programming language that promotes verbosity into one that celebrates terseness. In addition to making up for inadequate language design, this can also ease maintenance.

and, assuming you had a division column and mgrlastnm column in the employees table in your database, you could write code like Employee.find_by_division_and_mgrlastnm "Sales", "Simpson" and it would just work. The Rails metaprogramming logic would detect that such a function did not exist and would generate the missing function at run-time.

So how does all this relate to SQL? In this post and others, I will answer that question and attempt to illustrate the benefits of metaprogramming in SQL PL.

The Wikipedia article on metaprogramming defines it as "the writing of computer programs that write or manipulate other programs (or themselves) as their data, or that do part of the work at compile time that would otherwise be done at runtime." The article goes on to show an example of generative programming (the first half of metaprogramming) in bash script and references several programming languages with powerful metaprogramming facilities like Ruby, Python and C++.

Fortunately, you don't need a fancy dynamic language like Ruby or Python or a template metaprogramming language like C++ to do metaprogramming. You just need a language that can treat data as code. Thanks to dynamic SQL, most valid SQL statements in the form of data (i.e. as a string) can be interpreted as code at run-time.

For an example, we turn again to the writing of a generic comparison function. We will start with an EQ function that just compares integers and, step by step, we will convert it into a metaprogram that can create an EQ function for any simple type (I realize that the '=' operator does a fine job of this already and I will get to a more useful application in a later post when we look at ROW types). Step 1 is to write the end result we want, that is, the definition of an integer comparison function named EQ:

Step 2 is to turn the whole function into a string and execute that string as dynamic SQL. We'll put the logic that executes the string into its own function called CREATE_INTEGER_EQ_FUNCTION. We will use the EXECUTE IMMEDIATE statement to execute the dynamic SQL string:

I guess that, according to the Wikipedia definition, probably just about any practical use of dynamic SQL would count as metaprogramming. When I think of some of the examples of metaprogramming I’ve seen in languages like Ruby though, they seem to take things much further, which is what I’m hoping to demonstrate is possible in SQL with this series of blog posts. Thank you for your comment.

I guess that, according to the Wikipedia definition, probably just about any practical use of dynamic SQL would count as metaprogramming. When I think of some of the examples of metaprogramming I've seen in languages like Ruby though, they seem to take things much further, which is what I'm hoping to demonstrate is possible in SQL with this series of blog posts. Thank you for your comment.