Does Vertica have an equivalent to MySQL's GROUP_CONCAT function?

if ((myConcat.length() + otherConcat.length()) +1 <= maxSize) {  
   string word = myConcat.str();
   if (word.length()>0 && otherConcat.str().length()>0) word.append("|"); 
   word.append(otherConcat.str());
   myConcat.copy(word);
}

$> yum install g++

virtual void getParameterType(ServerInterface &srvInterface,                               SizedColumnTypes &parameterTypes)     {

      parameterTypes.addVarchar(10, "delimiter");     } virtual void initAggregate(ServerInterface &srvInterface,                           IntermediateAggs &intermediates)     {        ParamReader paramReader = srvInterface.getParamReader();        std::string delimiter = srvInterface.getParamReader().getStringRef("delimiter").str();     }    HTH Pratibha

Sorry for hijakacing the thread.

I have tried to modify the Concatenate.cpp file as you mentioned. It compiled successfully but it is not changing the delimeter.Below is my code.

Please help me on this. Also it there a way I can test (compile and run)the code in my local machine without intalling the fucntion so that I can check the functionally it is working or not.

/* Copyright (c) 2005 - 2014, Hewlett-Packard Development Co., L.P. -*- C++ -*- */
/*
* Description: Example User Defined Aggregate Function: Concatenate strings
*
*/

#include "Vertica.h"
#include <time.h>
#include <sstream>
#include <iostream>

using namespace Vertica;
using namespace std;

/**
* User Defined Aggregate Function concatenate that takes in strings and concatenates
* them together. Right now, the max length of the resulting string is ten times the
* maximum length of the input string.
*/
class Concatenate : public AggregateFunction
{

virtual void initAggregate(ServerInterface &srvInterface, IntermediateAggs &aggs)
{
try {
VString &concat = aggs.getStringRef(0);
concat.copy("");
} catch(exception& e) {
// Standard exception. Quit.
vt_report_error(0, "Exception while initializing intermediate aggregates: [%s]", e.what());
}
}

void aggregate(ServerInterface &srvInterface,
BlockReader &argReader,
IntermediateAggs &aggs)
{
try {
VString &concat = aggs.getStringRef(0);
string word = concat.str();
uint32 maxSize = aggs.getTypeMetaData().getColumnType(0).getStringLength();
do {
const VString &input = argReader.getStringRef(0);

if (!input.isNull()) {
if ((word.length() + input.length()) > maxSize) break;
word.append(input.str());
}
} while (argReader.next());
concat.copy(word);
} catch(exception& e) {
// Standard exception. Quit.
vt_report_error(0, "Exception while processing aggregate: [%s]", e.what());
}
}

virtual void combine(ServerInterface &srvInterface,
IntermediateAggs &aggs,
MultipleIntermediateAggs &aggsOther)
{
try {
uint32 maxSize = aggs.getTypeMetaData().getColumnType(0).getStringLength();
VString myConcat = aggs.getStringRef(0);

do {
const VString otherConcat = aggsOther.getStringRef(0);
if ((myConcat.length() + otherConcat.length())+1 <= maxSize) {
string word = myConcat.str();
if (word.length()>0 && otherConcat.str().length()>0) word.append(",");
word.append(otherConcat.str());
myConcat.copy(word);
}
} while (aggsOther.next());
} catch(exception& e) {
// Standard exception. Quit.
vt_report_error(0, "Exception while combining intermediate aggregates: [%s]", e.what());
}
}

virtual void terminate(ServerInterface &srvInterface,
BlockWriter &resWriter,
IntermediateAggs &aggs)
{
try {
const VString &concat = aggs.getStringRef(0);
VString &result = resWriter.getStringRef();

result.copy(&concat);
} catch(exception& e) {
// Standard exception. Quit.
vt_report_error(0, "Exception while computing aggregate output: [%s]", e.what());
}
}

InlineAggregate()
};

class ConcatenateFactory : public AggregateFunctionFactory
{
virtual void getIntermediateTypes(ServerInterface &srvInterface, const SizedColumnTypes &inputTypes, SizedColumnTypes &intermediateTypeMetaData)
{
int input_len = inputTypes.getColumnType(0).getStringLength();
intermediateTypeMetaData.addVarchar(input_len*10);
}

virtual void getPrototype(ServerInterface &srvfloaterface, ColumnTypes &argTypes, ColumnTypes &returnType)
{
argTypes.addVarchar();
returnType.addVarchar();
}

virtual void getReturnType(ServerInterface &srvfloaterface,
const SizedColumnTypes &inputTypes,
SizedColumnTypes &outputTypes)
{
int input_len = inputTypes.getColumnType(0).getStringLength();
outputTypes.addVarchar(input_len*10);
}

virtual AggregateFunction *createAggregateFunction(ServerInterface &srvfloaterface)
{ return vt_createFuncObject<Concatenate>(srvfloaterface.allocator); }

};

RegisterFactory(ConcatenateFactory);

It looks like the Concatenate.cpp example has disappeard in v9.1.1-0.
Trying to compile it from archived versions works fine, but on debian 9.6, the CREATE LIBRARY fails:

dbadmin=> CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
ROLLBACK 3399:  Failure in UDx RPC call InvokeGetLibraryManifest(): Exception calling getLibraryManifest() in User Defined Object [], message: basic_string::_S_create

Hi,

I grabbed the code from here:

https://github.com/vertica/UDx-Examples/blob/master/Java-and-C++/AggregateFunctions/Concatenate.cpp

I'm using CentOS, but I was able to compile it and create the Library/Function in Vertica 9.1.1-4:

[dbadmin@s18384357 ~]$ g++ -D HAVE_LONG_INT_64  -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value -fPIC -o Concatenate.so Concatenate.cpp /opt/vertica/sdk/include/Vertica.cpp

[dbadmin@s18384357 ~]$ ls Concatenate.so
Concatenate.so

[dbadmin@s18384357 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> SELECT version();
              version
------------------------------------
 Vertica Analytic Database v9.1.1-4
(1 row)

dbadmin=> CREATE LIBRARY AggregateFunctionsConcatenate AS '/home/dbadmin/Concatenate.so';
CREATE LIBRARY

dbadmin=> CREATE AGGREGATE FUNCTION agg_group_concat AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;
CREATE AGGREGATE FUNCTION

dbadmin=> SELECT * FROM colors;
 color
-------
 BLUE
 GREEN
 RED
(3 rows)

dbadmin=> SELECT rtrim(agg_group_concat(color || ','), ',') AS colors FROM colors;
      colors
------------------
 BLUE,GREEN,RED
(1 row)

Note that Vertica 9.1.1-4 now has a built-in function called LISTAGG that does the same thing...

dbadmin=> SELECT listagg(color) AS colors FROM colors;
     colors
----------------
 BLUE,GREEN,RED
(1 row)