Page History

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