6

I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried to re-use the built-in array_agg_transfn and array_agg_finalfn. Starting from the code example in the documentation, an initial version similar to array_agg works (as DB superuser):

CREATE AGGREGATE array_agg_z (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    finalfunc_extra
);

And it returns NULL on empty input as expected:

=> SELECT array_agg_z(i) IS NULL FROM (SELECT 0 WHERE 1 = 2) t(i);
 ?column? 
----------
 t
(1 row)

Now, to change array_agg_z() to return empty array on empty input, I attempted to set the initcond to '{}' as follows:

CREATE AGGREGATE array_agg_z (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    initcond = '{}',
    finalfunc_extra
);

Now it's giving an error:

=> SELECT array_agg_z(i) IS NULL FROM (SELECT 0 WHERE 1 = 2) t(i);
ERROR:  cannot accept a value of type internal

How to work around this?

Or do I have to hack the C source code related to array_agg() to make this variant?

By the way, this is with PostgreSQL 12.

2
  • 2
    Why don't you use coalesce(array_agg(...), '{}')?
    – user1822
    Commented May 14, 2020 at 4:57
  • @a_horse_with_no_name Indeed, that works too. I am just trying to avoid doing that (coalescing to different kinds of zeros) everywhere, if this is possible like custom aggregates.
    – tinlyx
    Commented May 14, 2020 at 6:23

1 Answer 1

1

Interesting question. I wrote a custom array_agg a number of weeks ago, and I can boil out what you seem to need.

First, we will put together an accumulator function, like so:

CREATE OR REPLACE FUNCTION array_agg_z_accum(anyarray, anynonarray)
RETURNS anyarray AS $$
BEGIN
    IF $1 IS NULL THEN
        RETURN ARRAY[$2];
    ELSE
        RETURN array_append($1, $2);
    END IF;
END;
$$ LANGUAGE plpgsql;

and then use it in our aggregate, replacing anynonarray with anyelement:

CREATE AGGREGATE array_agg_z(anyelement) (
  SFUNC = array_agg_mult_accum,
  STYPE = anyarray,
  INITCOND = '{}'
);

Finally:

SELECT array_agg_z(i) IS NULL FROM (SELECT 0 WHERE 1 = 2) t(i);

Produces:

?column?
FALSE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.