postgresql - array_agg for Array Types

Question

I'm trying to get array_agg to work with an array type in Postgresql and I'm having trouble figuring out if this is possible and if so how to do it. The pertinent part of my query looks like this:

array_agg(ARRAY[e.alert_type::text, e.id::text, cast(extract(epoch from e.date_happened) as text)] order by e.date_happened asc, e.id asc)

The error that I'm getting in response is ERROR: could not find array type for data type text[]

Is this possible or should I try to find another approach?

Thanks!

Answer 1

You could write custom aggregate to handle your specific array of arrays, e.g.:

DROP TABLE IF EXISTS e;
CREATE TABLE e
(
    id serial PRIMARY KEY,
    alert_type text,
    date_happened timestamp with time zone
);

INSERT INTO e(alert_type, date_happened) VALUES
    ('red', '2011-05-10 10:15:06'),
    ('yellow', '2011-06-22 20:01:19');

CREATE OR REPLACE FUNCTION array_agg_custom_cut(anyarray)
RETURNS anyarray
    AS 'SELECT $1[2:array_length($1, 1)]'
LANGUAGE SQL IMMUTABLE;

DROP AGGREGATE IF EXISTS array_agg_custom(anyarray);
CREATE AGGREGATE array_agg_custom(anyarray)
(
    SFUNC = array_cat,
    STYPE = anyarray,
    FINALFUNC = array_agg_custom_cut,
    INITCOND = $${{'', '', ''}}$$
);

Query:

SELECT
    array_agg_custom(
        ARRAY[
            alert_type::text,
            id::text,
            CAST(extract(epoch FROM date_happened) AS text)
        ])
FROM e;

Result:

              array_agg_custom              
--------------------------------------------
 {{red,1,1305036906},{yellow,2,1308787279}}
(1 row)

EDIT:

Here is second, shorter way (that is, you don't need array_agg_custom_cut function, but as you see additional ARRAY level is necessary in query):

CREATE AGGREGATE array_agg_custom(anyarray)
(
    SFUNC = array_cat,
    STYPE = anyarray
);

SELECT
    array_agg_custom(
        ARRAY[
            ARRAY[
                alert_type::text,
                id::text,
                CAST(extract(epoch FROM date_happened) AS text)
            ]
        ])
FROM e;

Result:

              array_agg_custom              
--------------------------------------------
 {{red,1,1305036906},{yellow,2,1308787279}}
(1 row)