Trafodion TMUDF(Table-Mapping UDF)

通过前面文章我们已经知道，Trafodin提供了几种UDR(User-Defined Routines )，包括SPJ存储过程,标量UDF等。本文则继续介绍一种UDR，称为TMUDF，即Table-Mapping函数。
与前两者不同的是，TMUDF主要用于FROM子句中，即它的返回值可以看成是一个SQL TABLE。TMUDF目前可以使用C++和Java两种语言实现。
下面就介绍下如何实现一个简单的TMUDF并调用它，以Java实现为例，

1 编写基础Java类，需要实现UDR接口
（注：UDR接口在$MY_SQROOT/export/lib/trafodion-sql*.jar）

import org.trafodion.sql.udr.*;

class HelloJavaWorld extends UDR
{
  // default constructor
  public HelloJavaWorld()
  {}

  // override the runtime method
  @Override
  public void processData(UDRInvocationInfo info,
                          UDRPlanInfo plan)
    throws UDRException
  {
    // set the output column
    info.out().setString(0, "Hello Java world!");

    // produce a single output row
    emitRow(info);
  }
}

2 编译Java类生成Class文件，并打包成Jar文件

[trafodion@cent-2 ~]$ /opt/jdk1.7.0_67/bin/javac  -cp trafodion-sql-1.3.0.jar tmudf.java
[trafodion@cent-2 ~]$ /opt/jdk1.7.0_67/bin/jar cvf HelloJavaWorld.jar HelloJavaWorld.class
[trafodion@cent-2 ~]$ ls -la HelloJavaWorld.*
-rw-rw-r-- 1 trafodion trafodion 696 Nov 15 17:23 HelloJavaWorld.class
-rw-rw-r-- 1 trafodion trafodion 854 Nov 15 17:25 HelloJavaWorld.jar

3 创建LIBRARY

SQL>create library helloworldlib file '/home/trafodion/HelloJavaWorld.jar';

--- SQL operation complete.

SQL>get libraries;

Libraries in Schema TRAFODION.SEABASE
=====================================

HELLOWORLDLIB

--- SQL operation complete.

4 创建Table Mapping UDF

SQL>create table_mapping function helloworld()
+>returns (col1 char(40))
+>external name 'HelloJavaWorld'
+>language java
+>library helloworldlib;

--- SQL operation complete.

SQL>get table_mapping functions;

Table_mapping functions in Schema TRAFODION.SEABASE
===================================================

HELLOWORLD

--- SQL operation complete.

5 调用UDF

SQL>select * from udf(helloworld());

COL1
----------------------------------------
Hello Java world!

--- 1 row(s) selected.

6 查看TMUDF定义

SQL>showddl table_mapping function helloworld;


CREATE TABLE_MAPPING FUNCTION TRAFODION.SEABASE.HELLOWORLD
  (
  )
  RETURNS
  (
    OUT COL1 CHAR(40) CHARACTER SET ISO88591
  )
  EXTERNAL NAME 'HelloJavaWorld'
  LIBRARY TRAFODION.SEABASE.HELLOWORLDLIB
  LANGUAGE JAVA
  NO SQL
  SAFE EXECUTION MODE
  ;

-- GRANT EXECUTE ON FUNCTION TRAFODION.SEABASE.HELLOWORLD TO DB__ROOT WITH GRANT OPTION;

--- SQL operation complete.