Trafodion 标量UDF

来源：互联网发布：用c语言编写杨辉三角编辑：程序博客网时间：2024/06/06 17:11

本文主通过一个例子（反转字符串）介绍一下如何在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.

1 0