Re: create aggregates to concatenate

> 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.
>

And, while somewhat off-topic but in a similar vein, although the following
goes against the SQL standard so dearly held to by the Postgresql team, I
found it useful in some cirumstances to circumvent the handling of NULL's in
text columns with

CREATE OR REPLACE FUNCTION public.textcat_null(text, text)
RETURNS text AS
'
SELECT textcat(COALESCE($1, \'\'), COALESCE($2, \'\'));
'
LANGUAGE 'sql' VOLATILE;

CREATE OPERATOR public.||(
PROCEDURE = "public.textcat_null",
LEFTARG = text,
RIGHTARG = text);