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.

Examples abound. A famous one is Ruby On Rails’ ActiveRecord class inspired by Martin Fowler’s ActiveRecord design pattern. At least in early versions of Rails, you could simply create an empty class like the following:

class Employee < ActiveRecord::Base
end

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:

<!--start_raw-->CREATE OR REPLACE FUNCTION EQ(LEFT INTEGER, RIGHT INTEGER)
CONTAINS SQL
RETURNS INTEGER
BEGIN
  DECLARE RESULT INTEGER;
  IF (LEFT = RIGHT) THEN
    SET RESULT = 1;
  ELSE
    SET RESULT = 0;
  END IF;
  RETURN RESULT;
END@<!--end_raw-->

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:

<!--start_raw-->CREATE OR REPLACE PROCEDURE CREATE_INTEGER_EQ_FUNCTION()
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  SET SQL =        'CREATE OR REPLACE FUNCTION EQ(LEFT INTEGER, RIGHT INTEGER) ';
  SET SQL = SQL || 'CONTAINS SQL ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || '  DECLARE RESULT INTEGER; ';
  SET SQL = SQL || '  IF (LEFT = RIGHT) THEN ';
  SET SQL = SQL || '    SET RESULT = 1; ';
  SET SQL = SQL || '  ELSE ';
  SET SQL = SQL || '  SET RESULT = 0; ';
  SET SQL = SQL || 'END IF; ';
  SET SQL = SQL || 'RETURN RESULT; ';
  SET SQL = SQL || 'END';
  EXECUTE IMMEDIATE SQL;
END@<!--end_raw-->

Now, when we execute CREATE_INTEGER_EQ_FUNCTION(), it will create our EQ function:

<!--start_raw-->call create_integer_eq_function
  Return Status = 0

values eq(1,2)
1          
-----------
          0
  1 record(s) selected.<!--end_raw-->

Step 3 is to generalize. We will replace CREATE_INTEGER_EQ_FUNCTION() with CREATE_EQ_FUNCTION_FOR(datatype VARCHAR(128)) so now we can call it like this: call CREATE_EQ_FUNCTION_FOR('INTEGER').

<!--start_raw-->CREATE OR REPLACE PROCEDURE CREATE_EQ_FUNCTION(DATATYPE VARCHAR(128))
MODIFIES SQL DATA
BEGIN
  DECLARE SQL VARCHAR(1024);
  SET SQL =        'CREATE OR REPLACE FUNCTION EQ(LEFT  ' || DATATYPE || ', ';
  SET SQL = SQL ||                               'RIGHT ' || DATATYPE || ') ';
  SET SQL = SQL || 'CONTAINS SQL ';
  SET SQL = SQL || 'RETURNS INTEGER ';
  SET SQL = SQL || 'BEGIN ';
  SET SQL = SQL || '  DECLARE RESULT INTEGER; ';
  SET SQL = SQL || '  IF (LEFT = RIGHT) THEN ';
  SET SQL = SQL || '    SET RESULT = 1; ';
  SET SQL = SQL || '  ELSE ';
  SET SQL = SQL || '  SET RESULT = 0; ';
  SET SQL = SQL || 'END IF; ';
  SET SQL = SQL || 'RETURN RESULT; ';
  SET SQL = SQL || 'END';
  EXECUTE IMMEDIATE SQL;
END@<!--end_raw-->

Now, if we want to create an EQ function for CHAR(1) instead, we just pass a different argument.

<!--start_raw-->call create_eq_function('char(1)') 

  Return Status = 0
values eq('a','a')
1          
-----------
          1
  1 record(s) selected.

values eq('a','b')
1          
-----------
          0
  1 record(s) selected.<!--end_raw-->

This works well for simple types like integers, varchars, and user-defined distinct types with comparisons, but cannot deal with ROW types and ARRAY types, which cannot be compared with the '=' operator. In Metaprogramming in SQL (Part 2), Metaprogramming in SQL (Part 3), and Metaprogramming in SQL (Part 4), I'll show you the real power of metaprogramming when we tackle these more challenging data types.