Value Generating Functions
ADD2
Add two integers
MMA5
Return the min, max, and average of five integers
NULL values are treated the same as a zero
REVERSE
Reverse a string
Input string must be VARCHAR and can be up to 32 characters
Note: If you are looking for table-valued functions, see here.
C source
cat > udf.c <<EOF
#include "sqludr.h"
SQLUDR_LIBFUNC SQLUDR_INT32 add2(SQLUDR_INT32 *in1,
SQLUDR_INT32 *in2,
SQLUDR_INT32 *out1,
SQLUDR_INT16 *inInd1,
SQLUDR_INT16 *inInd2,
SQLUDR_INT16 *outInd1,
SQLUDR_TRAIL_ARGS)
{
if (calltype == SQLUDR_CALLTYPE_FINAL)
return SQLUDR_SUCCESS;
if (SQLUDR_GETNULLIND(inInd1) == SQLUDR_NULL ||
SQLUDR_GETNULLIND(inInd2) == SQLUDR_NULL)
SQLUDR_SETNULLIND(outInd1);
else
(*out1) = (*in1) + (*in2);
return SQLUDR_SUCCESS;
}
SQLUDR_LIBFUNC SQLUDR_INT32 mma5(SQLUDR_INT32 *in1,
SQLUDR_INT32 *in2,
SQLUDR_INT32 *in3,
SQLUDR_INT32 *in4,
SQLUDR_INT32 *in5,
SQLUDR_INT32 *out1,
SQLUDR_INT32 *out2,
SQLUDR_INT32 *out3,
SQLUDR_INT16 *inInd1,
SQLUDR_INT16 *inInd2,
SQLUDR_INT16 *inInd3,
SQLUDR_INT16 *inInd4,
SQLUDR_INT16 *inInd5,
SQLUDR_INT16 *outInd1,
SQLUDR_INT16 *outInd2,
SQLUDR_INT16 *outInd3,
SQLUDR_TRAIL_ARGS)
{
int sum = 0, min = 0, max = 0, avg = 0;
int args[5];
int i;
if (calltype == SQLUDR_CALLTYPE_FINAL)
return SQLUDR_SUCCESS;
args[0] = (SQLUDR_GETNULLIND(inInd1) == SQLUDR_NULL ? 0 : *in1);
args[1] = (SQLUDR_GETNULLIND(inInd2) == SQLUDR_NULL ? 0 : *in2);
args[2] = (SQLUDR_GETNULLIND(inInd3) == SQLUDR_NULL ? 0 : *in3);
args[3] = (SQLUDR_GETNULLIND(inInd4) == SQLUDR_NULL ? 0 : *in4);
args[4] = (SQLUDR_GETNULLIND(inInd5) == SQLUDR_NULL ? 0 : *in5);
sum = min = max = args[0];
for (i = 1; i < 5; i++)
{
sum += args[i];
min = (args[i] < min ? args[i] : min);
max = (args[i] > max ? args[i] : max);
}
avg = sum / 5;
*out1 = min;
*out2 = max;
*out3 = avg;
return SQLUDR_SUCCESS;
}
/* 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;
}
EOF
Compile
gcc -g -Wall -I$MY_SQROOT/export/include/sql -shared -o udf.so udf.c
Register
Do this from the directory containing udf.so
UDFLIB="'$(pwd)/udf.so'"
sqlci <<EOF
create library myudfs file $UDFLIB;
-- ADD2
drop function add2;
create function add2(int,int) returns (add2 int)
external name 'add2' library myudfs
deterministic no sql no transaction required;
-- MMA5
drop function mma5;
create function mma5(int,int,int,int,int)
returns (mma_min int, mma_max int, mma_avg int)
external name 'mma5' library myudfs
deterministic no sql no transaction required;
-- REVERSE
drop function reverse;
create function reverse(varchar(32)) returns (reverse varchar(32))
external name 'reverse' library myudfs
deterministic no sql no transaction required;
EOF
Invoke
sqlci <<EOF
-- CREATE A TABLE
drop table t;
create table t (a int primary key not null, b int, c int, d int, e int, f varchar(32));
insert into t values (1,2,3,4,5,'abc'), (6,7,8,9,10,'def'), (11,12,13,14,15,'ghi');
-- ADD2
select a, b, add2(a,b) from t;
-- MMA5
select mma5(a,b,c,d,e) from t;
-- REVERSE
select * from t where reverse(f) > 'g';
EOF
SHOWDDL
sqlci <<EOF
showddl function add2;
showddl function mma5;
showddl function reverse;
EOF