Concatenate rows with a PostgreSQL aggregate function

Aggregates are a nifty feature of PostgreSQL that let you write your own functions that operate over a number of rows, similar to MIN, MAX, COUNT and AVERAGE.

Basically, a set of rows (typically generated via GROUP BY) are iterated through the Aggregate function, with a final call when the set of rows is complete.

• MIN works by keeping track of the smallest value passed in
• COUNT works by incrementing a counter for each row
• AVERAGE works like SUM and then divides by the equivalent to COUNT

I had a need to concatenate strings from multiple rows into a single string, which I wanted to delimit by blank lines, commas or whatever. For example, let’s say I had some rows:

NAME
----
John
David
Mary
Sally

I wanted to be able to call smoosh(name, ', ') and get the result: John, David, Mary, Sally

The SMOOSH function below does just that:

-- SMOOSH: Combines strings with supplied paramter
-- eg smoosh('fred', 'joe', ', ') ==> 'fred, joe'
-- The 'strict' paramter automatically handles NULL inputs

CREATE FUNCTION smoosh(text, text, text) RETURNS text
  VOLATILE STRICT
AS $$$
DECLARE
  BEGIN
    IF  character_length($1) > 0 THEN
      RETURN $1 || $3 || $2;
    ELSE
      RETURN $2;
    END IF;
  END;
$$$ LANGUAGE plpgsql;

-- Combine text with supplied delimiter
-- eg smoosh(name, ', ')
CREATE AGGREGATE smoosh(text, text)
(
  SFUNC = smoosh,
  STYPE = text
);

-- TEST HERE
/*
select smoosh(i::text, ', ')
FROM
  (select generate_series(1, 3) as i UNION select null as i UNION select 4 as i) foo
*/

Aggregates take a bit of brain power to interpret, but basically:

• The Smoosh FUNCTION concatenates two input strings, with a supplied delimiter in the middle
• The Smoosh AGGREGATE just tells PostgreSQL to call the Smoosh FUNCTION for each row, and that it’s of type text

Neat, eh?!

Advertisement