Trafodion 标量UDF

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