Implementing function transforms using SQL-bodied routines

In your SQL-bodied function, you can use constructors, observers, and mutators to achieve the transformation. This SQL-bodied transform intervenes between the SQL statement and the external function. The FROM SQL transform takes the object as an SQL parameter and returns a row of values representing the attributes of the structured type. The following example contains a possible FROM SQL transform function for an address object using a SQL-bodied function:

   CREATE FUNCTION addresstofunc (A Address_t) 1
      RETURNS ROW  (Street VARCHAR(30), Number CHAR(15),
         City VARCHAR(30), State VARCHAR(10)) 2

      LANGUAGE SQL 3
      RETURN VALUES (A..Street, A..Number, A..City, A..State) 4

The following list explains the syntax of the preceding CREATE FUNCTION statement:

1. The signature of this function indicates that it accepts one parameter, an object of type Address_t.
2. The RETURNS ROW clause indicates that the function returns a row containing four columns: Street, Number, City, and State.
3. The LANGUAGE SQL clause indicates that this is an SQL-bodied function, not an external function.
4. The RETURN clause marks the beginning of the function body. The body consists of a single VALUES clause that invokes the observer method for each attribute of the Address_t object. The observer methods decompose the object into a set of base types, which the function returns as a row.

Db2® does not know that you intend to use this function as a transform function. Until you create a transform group that uses this function, and then specify that transform group in the appropriate situation, Db2 cannot use the function as a transform function.

The TO SQL transform simply does the opposite of the FROM SQL function. It takes as input the list of parameters from a routine and returns an instance of the structured type. To construct the object, the following FROM SQL function invokes the constructor function for the Address_t type:

   CREATE FUNCTION functoaddress (street VARCHAR(30), number CHAR(15),
                                  city VARCHAR(30), state VARCHAR(10)) 1
      RETURNS Address_t 2
      LANGUAGE SQL
      CONTAINS SQL
      RETURN 
         Address_t()..street(street)..number(number)
         ..city(city)..state(state) 3

The following list explains the syntax of the previous statement:

1. The function takes a set of base type attributes.
2. The function returns an Address_t structured type.
3. The function constructs the object from the input types by invoking the constructor for Address_t and the mutators for each of the attributes.

The order of parameters in the FROM SQL function does not matter, other than that all functions that return addresses using this transform function must use this same order.