Netezza Performance Server

 View Only
  • 1.  looking for the source code for the group_concat UDF

    Posted Wed March 11, 2020 01:57 PM
    Hello,
    I am looking for the current version of the source code for the group_concat UDF.  I understand it used to be on the old DeveloperWorks.  Please advise.
    Thanks,
    Mike

    ------------------------------
    Michael Metts
    ------------------------------


  • 2.  RE: looking for the source code for the group_concat UDF

    Posted Thu May 07, 2020 11:55 AM
    Maybe the link below helps.  It mentions that the UDF GROUP_CONCAT comes with INZA, but is not installed by default.  It has instructions on how to install it. (hint: it mentions /nz/extensions/nz/nzlua/examples (here is the list of available optional functions))

    https://www.ibm.com/support/pages/installing-udfs-pda



    ------------------------------
    Chris Rodgers
    ------------------------------



  • 3.  RE: looking for the source code for the group_concat UDF

    Posted Mon May 11, 2020 11:19 AM
    // (c) 2013 IBM Corporation.
    // This source code is provided free of charge by IBM Corporation as a
    // convenience to its customers. This source code is provided "AS-IS" with
    // no warranty whatsoever. The customer accepts all risk in connection
    // with the use of this script, and IBM Corporation shall have no
    // liability whatsoever.

    // Usage: select group_concat(string) from <table> group by columns;


    #include "udxinc.h"
    #include <string.h>
    #include <list>

    #define DATA_DELIM 0
    #define OUT_DELIM ','

    using namespace nz::udx;

    class GroupConcat : public Uda
    {
    public:
    static Uda* instantiate();

    void initializeState()
    {
    setStateNull(0, true );
    }

    void accumulate()
    {
    if( isArgNull(0) ) return;

    StringArg* a = stringArg(0);
    StringArg* s = stringState(0);

    if( isStateNull(0)) {
    setStateNull(0, false );

    int len = a->length;
    if (len > s->dec_length)
    len = s->dec_length;
    memcpy(s->data, a->data, len);
    s->length = len;
    return;
    }

    int curlen = s->length;
    if(curlen >= s->dec_length)
    return;

    s->data[curlen] = DATA_DELIM;
    curlen++;

    int len=a->length;
    if( len + curlen > s->dec_length )
    len = s->dec_length - curlen;

    memcpy( s->data + curlen, a->data, len );
    s->length = curlen + len;
    }

    void merge()
    {
    if( isArgNull(0) ) return;

    StringArg* a = stringArg(0);
    StringArg* s = stringState(0);

    if( isStateNull(0)) {
    setStateNull(0, false );
    memcpy(s->data, a->data, a->length);
    s->length = a->length;
    return;
    }

    int curlen = s->length;
    if(curlen >= s->dec_length)
    return;

    s->data[curlen] = DATA_DELIM;
    curlen++;

    int len = a->length;
    if( len + curlen > s->dec_length )
    len = s->dec_length - curlen;

    memcpy( s->data + curlen, a->data, len );
    s->length = curlen + len;
    }

    ReturnValue finalResult()
    {
    std::string *str;
    std::list<std::string> values;

    if( isArgNull(0)) NZ_UDX_RETURN_NULL();

    StringReturn *r = stringReturnInfo();
    StringArg *s = stringArg(0);

    if( r->size < s->length )
    throwUdxException( "GROUP_CONCAT(): Result size too large!" );

    r->size = s->length;

    int len=0;
    char *p = s->data;

    for( int i=0; i<s->length; i++ )
    {
    if(s->data[i] != DATA_DELIM) {
    len++;
    } else {
    str = new string(p, len);
    values.push_back(*str);
    p = s->data + i + 1;
    len = 0;
    }
    }

    str = new string(p,len);
    values.push_back(*str);

    values.sort();

    setReturnNull(false);

    len = 0;
    while(!values.empty())
    {
    if( len>0 )
    r->data[len++] = OUT_DELIM;

    std::string tmp = values.front();
    memcpy(r->data+len, tmp.c_str(), tmp.length());
    len += tmp.length();
    values.pop_front();
    }

    NZ_UDX_RETURN_STRING(r);
    }
    };

    Uda* GroupConcat::instantiate()
    {
    return new GroupConcat;
    }

    ------------------------------
    Thomas Dennison
    ------------------------------