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!

Grzegorz Szpetkowski · Accepted Answer · 2011-07-22T14:08:20.967

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)

Thanks for the tip. I used the second approach, except that I created a new polymorphic function - array_append(anyarray,anyarray) - which just does 'SELECT array_cat($1, ARRAY[$2])', and then used array_append as my custom aggregate function's SFUNC. That way the outer ARRAY[] wrapper isn't needed, making the user-visible SQL appear simpler. :) — dannysauer, Mar 11 '13 at 18:00

score 4 · Answer 2 · answered Dec 18 '12 at 17:49

4

or cast the array to text like array_agg(array[xxx, yyy]::text)

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

answered Dec 18 '12 at 17:49

girgen

119
6

2

Work as a charm as long as you use a regexp_replace() afterward to get rid of unwanted " characters. No custom function needed so more portable i guess. – MarHoff Oct 27 '15 at 15:46

array_agg for Array Types

2 Answers2

Linked