Обсуждение: 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.

javier wilson
guegue.com

> 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);

>> 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);
>

Slightly less off-topic:

-- Try this

CREATE TABLE country (country_name varchar(64) NOT NULL);

INSERT INTO country VALUES ('Afghanistan');
INSERT INTO country VALUES ('Albania');
INSERT INTO country VALUES ('Algeria');
INSERT INTO country VALUES ('Andorra');
INSERT INTO country VALUES ('Angola');
INSERT INTO country VALUES ('Anguilla');
INSERT INTO country VALUES ('Argentina');
INSERT INTO country VALUES ('Armenia');
INSERT INTO country VALUES ('Aruba');
INSERT INTO country VALUES ('Ascension');
INSERT INTO country VALUES ('Australia');
INSERT INTO country VALUES ('Austria');

-- ... etc., etc.

CREATE AGGREGATE concat (
    BASETYPE = text,
    SFUNC = textcat,
    STYPE = text,
    INITCOND = ''
);


SELECT TRIM(', ' FROM (SELECT CONCAT(country_name||', ') FROM COUNTRY));

-- to get a comma-separated list of country names.