Implementing function transforms using SQL-bodied routines
About this task
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:
- The signature of this function indicates that it accepts one parameter,
an object of type
Address_t
. - The RETURNS ROW clause indicates that the function returns a row
containing four columns:
Street
,Number
,City
, andState
. - The LANGUAGE SQL clause indicates that this is an SQL-bodied function, not an external function.
- 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:
- The function takes a set of base type attributes.
- The function returns an
Address_t
structured type. - 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.