Tuesday, 5 October 2010

How to aggregate arrays into an array in PostgreSQL

I had to aggregate a bunch of text into a nested array using PostgreSQL 8.3.x but I found no existing solution. Creating this aggregate function worked for me.

First, create function for concatenating two arrays:

CREATE OR REPLACE FUNCTION array_to_array_cat(anyarray, anyarray)
RETURNS anyarray AS
select $1 || $2;

Then create aggregate function that is based on array concatenation function:

CREATE AGGREGATE array_to_array_agg(anyarray) (
SFUNC = array_to_array_cat,
STYPE = anyarray

Now, let's test it.
Array concatenation function:

SELECT array_to_array_cat(ARRAY[[1,2,3]], ARRAY[[7,8,9]]); --"{{1,2,3},{7,8,9}}"

SELECT array_to_array_cat(ARRAY[['foo','bar']], ARRAY[['ice','cream']]); --"{{foo,bar},{ice,cream}}"

Aggregate arrays from a column in a nested array from 'mytable':

SELECT array_to_array_agg(ARRAY[[col_a::varchar, col_b::varchar, col_c::varchar, col_d::varchar]])
FROM mytable;

Might return something like:
"{{c.1270,1270,SAC,"Description of SAC"},{1672,1672,HTR,"Description of HTR"}}"

N.B. This will work only if nested arrays have equal number of elements!

1 comment:

Maciej Bąk said...

Nice one, doesn't array_cat() shouldn't support array aggregation?