How to change array_agg() to return empty array on empty input in PostgreSQL?

Question

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.

@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
– tinlyx, Commented May 14, 2020 at 6:23

Jeff Vermeer · Accepted Answer · 2024-03-28 02:59:43Z

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

Stack Exchange Network

How to change array_agg() to return empty array on empty input in PostgreSQL?

1 Answer 1

Hot Network Questions

How to change array_agg() to return empty array on empty input in PostgreSQL?

1 Answer 1

Related

Hot Network Questions