Supporting user-defined aggregate functions

[gerdemb]

Summary

Supporting user-defined aggregate functions

Additional context

I have a custom type and a user-defined aggregate function that works on that type.

CREATE TYPE public.amount AS (
	number numeric,
	currency text
);

CREATE FUNCTION public.sum(state public.amount[], current public.amount) RETURNS public.amount[]
    LANGUAGE plpgsql
    AS $$
DECLARE
    found boolean = false;
    i int = 0;
BEGIN
    IF array_length(state, 1) IS NULL THEN
        RETURN array[current];
    END IF;

    FOR i IN 1..array_length(state, 1)
    LOOP
        IF state[i].currency = current.currency THEN
            state[i].number := state[i].number + current.number;
            found := true;
            EXIT;
        END IF;
    END LOOP;
    
    IF NOT found THEN
        state := array_append(state, current);
    END IF;

    RETURN state;
END;
$$;

CREATE AGGREGATE public.sum(public.amount) (
    SFUNC = public.sum,
    STYPE = public.amount[],
    INITCOND = '{}'
);

Unsurprisingly, columns using the custom amount type are not recognized as aggregatable by the plugin. I couldn't find any mention of user-defined aggregate functions in the documentation so I assume they are not supported. Would it be easy to add support? Any simple workarounds here to add support?

[benjie]

No we don’t support it, but yes I’d like to. I would need to invest time to figure out the best route forward and alas that’s the resource that is most scarce for me currently. Please feel free to have a go at it, but it may be easier in a few months time when the codebase is better documented.