notes/postgres/aggregate.md

CREATE OR REPLACE FUNCTION public.jsonb_concat(
    state jsonb,
    concat jsonb)
  RETURNS jsonb AS
$BODY$
BEGIN
    --RAISE notice 'state is %', state;
    --RAISE notice 'concat is %', concat;
    RETURN state || concat;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


CREATE OR REPLACE FUNCTION public.jsonb_concat_distinct_arr(
    state jsonb,
    concat jsonb)
  RETURNS jsonb AS
$BODY$
BEGIN
    --RAISE notice 'state is %', state;
    --RAISE notice 'concat is %', concat;
    RETURN SELECT jsonb_agg(state || concat;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


DROP AGGREGATE IF EXISTS public.jsonb_arr_aggc(jsonb);
CREATE AGGREGATE public.jsonb_arr_aggc(jsonb) (
  SFUNC=public.jsonb_concat,
  STYPE=jsonb,
  INITCOND='[]'
);

DROP AGGREGATE IF EXISTS public.jsonb_obj_aggc(jsonb);
CREATE AGGREGATE public.jsonb_obj_aggc(jsonb) (
  SFUNC=public.jsonb_concat,
  STYPE=jsonb,
  INITCOND='{}'
);