Aggregating row data using user-defined aggregate in Informix IDS

Implementing mysql group_concat() aggregate in Informix IDS 11.5 - thanks to Jonathan Leffler.

As DBA do:

CREATE FUNCTION gc_init(dummy varchar(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || ',' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    RETURN partial1 || ',' || partial2;
END FUNCTION;

CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);

Then you need to grant access to the functions to users that need it:

grant execute on function gc_init to appsql;
grant execute on function gc_iter to appsql;
grant execute on function gc_comb to appsql;
grant execute on function gc_fini to appsql;


Example Use:

select ord_id, group_concat(product)
from ord
group by ord_id