change brackets for array aggregation in postgres

Question

I like to change the brackets "{" and "}" to "[" and "]" in the following example. I have a table A with two coloumns, one is text_1 of type string and the second is count of type bigint. What I'm trying to do is to return a matrix notation like [[1,2,4],[2,4,5],...].

CREATE AGGREGATE array_agg_mult(anyarray) (
    SFUNC = array_cat,
    STYPE = anyarray,
    INITCOND = '{}'
);

WITH B AS(
SELECT 
    array_agg(count) AS count 
FROM
    A 
GROUP BY
    text_1
)
SELECT
    array_agg_mult(ARRAY[count]) 
FROM
    B;

Besides how to update array_agg_mult, if I try to change INITCOND = '{}' to INITCOND = '[]' I get the

ERROR: function "array_agg_mult" already exists with same argument types

Maybe there is a smart solution by using json generation with postgres.

Answer 1

Those brackets have nothing to do with the aggregation. Those are just the output format of the array type. A new aggregate function won't change that.

You can verify that by simply doing a

select array[1,2,3]

which will display:

array  
-------
{1,2,3}

The '{}' in the INITCOND simply means "empty array".

See the manual for details: https://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-IO

The only way to change that display would be to change the output method for arrays in general. I don't think this can be done without hacking the Postgres sources (and which would probably break many things)

If you want to display arrays in a different format, write your own function to do that.

create function format_array(p_array anyarray)
  returns text
as
$$
  select translate(p_array::text, '{}', '[]');
$$
language sql;

Then you can use that on any array:

select format_array(array[1,2,3]), 
       format_array('{}'::int[]), 
       format_array(array[[1,2,3],[4,5,6]]);

will output:

format_array | format_array | format_array     
-------------+--------------+------------------
[1,2,3]      | []           | [[1,2,3],[4,5,6]]

Of course you can apply more "cosmetics" in the format_array() function then just replacing {} with []

---

Your query would then become:

WITH B AS
(
  SELECT array_agg(count) AS count 
  FROM A 
  GROUP BY text_1
)
SELECT format_array(array_agg_mult(ARRAY[count]))
FROM B;