create aggregates to concatenate - Mailing list pgsql-general

i just wanted to share this with you, i wanted to do something like
this for a long time but just recently found out about "create
aggregate" reading old posts, so here it is, using user-defined
aggregate functions to concatenate results.

when it's numbers i usually use SUM to compute totals, but when it's
text you can create your own aggregate function to concatenate:

CREATE FUNCTION concat (text, text) RETURNS text AS $$
  DECLARE
    t text;
  BEGIN
    IF character_length($1) > 0 THEN
      t = $1 ||', '|| $2;
    ELSE
      t = $2;
    END IF;
    RETURN t;
  END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE pegar (
  sfunc = concat,
  basetype = text,
  stype = text,
  initcond = ''
);

then, for instance to list the countries names followed by the cities
in those countries as a comma separated list, you can use something
like (assuming you have those tables and "pais" is a foreign key in...
etc):

SELECT paises.pais, pegar(ciudad) FROM ciudades JOIN paises ON
ciudades.pais=paises.pais GROUP BY paises.pais

if i'm missing something or doing something wrong please let me know,
this is my first aggregate function.

javier wilson
guegue.com