Implementing client transforms using external UDFs

For example, assume that you wrote external UDFs that do the appropriate encoding and decoding for an address. Suppose that you named the FROM SQL client transform from_sql_to_client and the TO SQL client transform to_sql_from_client. In both of these cases, the output of the functions are in a format that can be used as input by the appropriate FROM SQL and TO SQL function transforms.

   CREATE FUNCTION from_sql_to_client (Address_t)
      RETURNS VARCHAR (150)
      LANGUAGE C
      TRANSFORM GROUP func_group
      EXTERNAL NAME 'addressudf!address_from_sql_to_client'
      DETERMINISTIC
      NO EXTERNAL ACTION
      NOT FENCED
      NO SQL
      PARAMETER STYLE SQL;

The DDL in the previous example makes it seem as if the from_sql_to_client UDF accepts a parameter of type Address_t. What really happens is that, for each row for which the from_sql_to_client UDF is invoked, the Addresstofunc transform decomposes the Address into its various attributes. The from_sql_to_client UDF produces a simple character string and formats the address attributes for display, allowing you to use the following simple SQL query to display the Name and Address attributes for each row of the Person table:

   SELECT Name, from_sql_to_client (Address)
   FROM Person;

Notice that the DDL in from_sql_to_client includes a clause called TRANSFORM GROUP. This clause tells Db2® which set of transforms to use in processing the address type in those functions.