vertica自定义函数C++版

Vertica 6.x 只支持R、C++编写Vertica的UDF，由于使用内置的String 提供的函数实现不了substring_index功能，且用C++实现

#include <algorithm>
#include <string>
#include "Vertica.h"
using namespace Vertica;
using namespace std;
// 从text中获取第|delim_num|个|delim|前的全部子串
std::string getSubString(const std::string& text, const std::string& delim, int delim_num) {
// 需要0个分隔符
if (delim_num <= 0) {
return std::string();
}
// 查找第n个分隔符
size_t delim_size = delim.size();
size_t next = 0;
do {
next = text.find(delim, next);
if (next != std::string::npos) {
--delim_num;
next+=delim_size;
}
} while (next <= text.size() && delim_num > 0);
return text.substr(0, next - delim_size);
}
/*
*
*/
class SubstringIndex : public ScalarFunction
{
public:
virtual void processBlock(ServerInterface &srvInterface,
BlockReader &arg_reader,
BlockWriter &res_writer) {
if (arg_reader.getNumCols() != 3)
vt_report_error(0, "Function only accept 3 arguments, but %zu provided", arg_reader.getNumCols());
// While we have inputs to process
try{
do {//C++实现UDF特有的（Java不需要），避免出现：ERROR 5400:
User-defined Scalar Function substring_index produced fewer output rows (0) than input rows (1)
string src
= arg_reader.getStringRef(0).str();
string delim = arg_reader.getStringRef(1).str();
vint
occur = arg_reader.getIntRef(2);
int
tot
= occur;
string result = getSubString(src,delim,tot);
res_writer.getStringRef().copy(result);
res_writer.next();
} while (arg_reader.next());
}catch(...){//这里需要捕捉异常，不然Vertica后台会退出，整个db就stop了，PS：C++的异常太复杂，...表示捕捉所有异常
res_writer.getStringRef().copy("OTHER");
}
}
};
class SubstringIndexFactory : public ScalarFunctionFactory
{
virtual ScalarFunction *createScalarFunction(ServerInterface &interface)
{ return vt_createFuncObj(interface.allocator, SubstringIndex); }
virtual void getPrototype(ServerInterface &interface,
ColumnTypes &argTypes,
ColumnTypes &returnType)
{
argTypes.addVarchar();
argTypes.addVarchar();
argTypes.addInt();
returnType.addVarchar();
}
virtual void getReturnType(ServerInterface &srvInterface,
const SizedColumnTypes &argTypes,
SizedColumnTypes &returnType)
{
const VerticaType &t = argTypes.getColumnType(0);
returnType.addVarchar(t.getStringLength());
}
};
RegisterFactory(SubstringIndexFactory);

dwyyproduct=> select substring_index('a/b/c','/',2);
substring_index
-----------------
a/b
(1 row)
dwyyproduct=> select substring_index('a/b/c','/',3);
substring_index
-----------------
a/b/c
(1 row)
dwyyproduct=> select substring_index('a/b/c','/',4);
substring_index
-----------------
a/b/c
(1 row)
dwyyproduct=> select substring_index('a/b/c','/',0);
substring_index
-----------------
(1 row)

 与JAVA实现不同，请注意上述功能代码的注释部分！

编写完cpp，在linux下执行gcc命令

gcc substring_index.cpp Vertica.cpp -I /opt/vertica/sdk/include/ -fPIC -shared -o verticaso.so

 /opt/vertica/sdk/include/ 为vertica c++ 的 .h 文件所在位置

verticaso.so 为生成的so文件

使用dbadmin进入vsql，执行

CREATE LIBRARY verticasolib AS '/tmp/verticaso.so' language 'C++';
CREATE FUNCTION substring_index AS LANGUAGE 'C++' NAME 'SubstringIndexFactory' LIBRARY verticasolib;

ALL DONE！