Re: [GENERAL] 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);
>
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.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] 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); ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
