Actian Community Forum

geraintjones · 2012-02-06

Hi,
I've an aggregate function in OME that looks to be working ok, eg:

Code:

 select cast(string_agg(table_name) as varchar(200)) from iitables;\g
  Executing . . .
   
  col1                                                                                                                       
    iiprotect,iiddb_netcost,iirole,iiviews,iisynonyms,ii_joinspecs,lgmo_lpb,iirolegrants,ii_rcommands,ii_dbd_rightslist,iirule,iiextended_relation,iikey_columns,iilpartitions,ii_sequence_values,iiprocedur
   
(1 row)

I'd like to extend this to add an optional second parameter to the function, so that an alternative delimiter to ',' can be specified. Trouble is, I can't get the function to recognise this additional parameter.

I've cut out (hopefully all of) the relevant bits of the existing code, below.
Adding a parameter II_DATA_VALUE *p2 to the function, and changing fid_attributes in the FI to II_FID_F128_VARARGS, allows the second parameter to be specified, but the string doesn't seem to be appearing in p2 within the function in the same way that p1 does. If I attempt to find its length with *(unsigned short *) p2->db_data, then I get E_SC0206.

Any suggestions as to what I'm doing wrong?

TIA, Geraint

------------------------------------

Code:

#define MAX_VARCHAR_LENGTH 32000

static II_DT_ID  UD_2_VC[]         = {II_VARCHAR,  II_VARCHAR};

Code:

 /* IIADD_FO_DFN */
   
      {
      II_O_OPERATION,       /*fod_object_type*/
      {"string_agg"},              /*fod_name*/
      UDF_STRING_AGG,    /*fod_id*/
      II_AGGREGATE             /*fod_type*/
      },

Code:

 /* IIADD_FI_DFN */
   
      {
      II_O_FUNCTION_INSTANCE,  /* fid_object_type */
      UDF_FI_STRING_AGG_VC,     /* fid_id*/
      II_NO_FI,                             /* fid_cmplmnt*/
      UDF_STRING_AGG,               /* fid_opid=fod_id from function definition */
      II_AGGREGATE,                    /* fid_optype  */
      II_FID_F0_NOFLAGS ,           /* fid_attributes*/
      0,                                       /* fid_wslength*/
      1,                                       /* fid_numargs*/
      UD_2_VC,                           /* fid_args, a pointer to an array of datatypes */
      II_VARCHAR,                      /* fid_result, result is a varchar */
      II_RES_FIXED,                    /* fid_rltype*/
      MAX_VARCHAR_LENGTH,    /* fid_rlength */
      II_PSVALID,                       /* fid_rprec */
      string_agg,                        /* fid_routine */
      0                                      /* lenspec_routine */
      }, /* string_agg() */

Code:

II_STATUS string_agg(
    II_SCB          *scb,
    II_DATA_VALUE   *rdv,
    II_DATA_VALUE   *p1
)
{
  char msg[500];
  char addstr[2]="";
  char *str1, *str2;
  unsigned short *str1len,*str2len;
  int addlen;

  str1len=(unsigned short *) rdv->db_data;
  str2len=(unsigned short *) p1->db_data;
  str1 = (char *) (rdv->db_data)+sizeof(short);
  str2 = (char *) (p1->db_data)+sizeof(short);

  addlen=0;
  if (*str1len>0)
  {
    addlen++;
    sprintf(addstr,",");
  }
  addlen+=(*str2len);

  if ((*str1len)+addlen > MAX_VARCHAR_LENGTH)
  {
    sprintf(msg,"Aggregated string exceeds max varchar length");
    us_error(scb, 0x22022, msg);
    return(II_ERROR);
  }
  (*str1len)+=addlen;
  strcat(str1,addstr);
  strcat(str1,str2);

  return(II_OK);
}

kschendel · 2012-02-06

You're not doing anything wrong. It appears that the call-out to the user aggregate function in adeexecute.c is hardwired to a single parameter.

There is a flag, 0x10 (16), that when set in fid_attributes (a.k.a adi_fiflags), indicates an OLAP function which expects 2 argments: status = (*func)(adfcb, &dv1, &dv2, aggstruct); where aggstruct is a pointer to an ADF_AG_OLAP. Whatever that is -- it's defined in common/hdr/hdr/adf.h but I didn't look to see. You can try setting the OLAP flag to see what happens.

You could also maybe looking into adding code into adeexecute.c to query the number of parameters and issue a multi-parameter function call, but I have a nagging suspicion that OPF / OPC also has a hardwired assumption of a single parameter. I'd like to be wrong on that.

geraintjones · 2012-02-07

Thanks Karl, that certainly gives me something to work on. I'll post back if I get anywhere with it.