本文主通过一个例子(反转字符串)介绍一下如何在Trafodion中创建并使用标量UDF。主要步骤如下,
1 准备c语言脚本
#include "sqludr.h"
/* Helper function to reverse a string */
static void reverseBytes(void *out, void *in, unsigned int numBytes)
{
int i;
char *pOut = (char *) out;
char *pIn = (char *) in;
for (i = 0; i < numBytes; i++)
pOut[i] = pIn[numBytes - (i + 1)];
}
SQLUDR_LIBFUNC SQLUDR_INT32 reverse(SQLUDR_VC_STRUCT *in1,
SQLUDR_VC_STRUCT *out1,
SQLUDR_INT16 *inInd1,
SQLUDR_INT16 *outInd1,
SQLUDR_TRAIL_ARGS)
{
if (calltype == SQLUDR_CALLTYPE_FINAL)
return SQLUDR_SUCCESS;
if (SQLUDR_GETNULLIND(inInd1) == SQLUDR_NULL)
{
SQLUDR_SETNULLIND(outInd1);
return SQLUDR_SUCCESS;
}
reverseBytes(out1->data, in1->data, in1->length);
out1->length = in1->length;
return SQLUDR_SUCCESS;
}
2 编译上述c程序生成.so文件
[trafodion@cent-2 ~]$ gcc -g -Wall -I$MY_SQROOT/export/include/sql -shared -o udf.so udf.c
[trafodion@cent-2 ~]$ ls -la udf.*
-rw-rw-r-- 1 trafodion trafodion 867 Nov 15 15:48 udf.c
-rwxrwxr-x 1 trafodion trafodion 11024 Nov 15 15:49 udf.so
3 创建LIBRARY
SQL>create library myudfs file '/home/trafodion/udf.so';
--- SQL operation complete.
SQL>get libraries;
Libraries in Schema TRAFODION.SEABASE
=====================================
MYUDFS
--- SQL operation complete.
4 创建UDF
SQL>create function reverse(varchar(32)) returns (reverse varchar(32))
+>external name 'reverse' library myudfs
+>deterministic no sql no transaction required;
--- SQL operation complete.
SQL>get functions;
Functions in Schema TRAFODION.SEABASE
=====================================
REVERSE
--- SQL operation complete.
5 执行UDF
SQL>select reverse('abcdefg') from (values(1));
REVERSE
--------------------------------
gfedcba
--- 1 row(s) selected.
6 查看FUNCTION定义
SQL>showddl function reverse;
CREATE FUNCTION TRAFODION.SEABASE.REVERSE
(
IN VARCHAR(32) CHARACTER SET ISO88591
)
RETURNS
(
OUT REVERSE VARCHAR(32) CHARACTER SET ISO88591
)
EXTERNAL NAME 'reverse'
LIBRARY TRAFODION.SEABASE.MYUDFS
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NO FINAL CALL
NO STATE AREA
ALLOW ANY PARALLELISM
DETERMINISTIC
SAFE EXECUTION MODE
;
-- GRANT EXECUTE ON FUNCTION TRAFODION.SEABASE.REVERSE TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.