pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedString aggregate function
6+ messages / 2 participants
[nested] [flat]
* String aggregate function
@ 2003-03-17 08:01 Objectz <objectz@postmark.net>
0 siblings, 1 reply; 6+ messages in thread
From: Objectz @ 2003-03-17 08:01 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I want to make an aggregate function that concatenates strings from a
certain column into one cell in a group by clause.
For example I have the following table :
Type Text
=================
1 text1
2 text2
1 text3
3 text4
2 text5
I need a query that group by type and concats the text columns to
produce something like the following :
1 text1, text3
2 text2, text5
3 text4
I am aware that this can be done through recursive joins but this is too
complicated and heavy.
Any suggestions?
Regards
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: String aggregate function
@ 2003-03-17 09:16 Tomasz Myrta <jasiek@klaster.net>
parent: Objectz <objectz@postmark.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tomasz Myrta @ 2003-03-17 09:16 UTC (permalink / raw)
To: Objectz <objectz@postmark.net>; +Cc: pgsql-sql
Objectz wrote:
> Hi all,
>
> I want to make an aggregate function that concatenates strings from a
> certain column into one cell in a group by clause.
> For example I have the following table :
>
> Type Text
> =================
> 1 text1
> 2 text2
> 1 text3
> 3 text4
> 2 text5
>
> I need a query that group by type and concats the text columns to
> produce something like the following :
>
> 1 text1, text3
> 2 text2, text5
> 3 text4
>
> I am aware that this can be done through recursive joins but this is too
> complicated and heavy.
>
> Any suggestions?
>
> Regards
Look at this site (Aggregate Functions):
http://www.brasileiro.net:8080/postgres/cookbook/
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: String aggregate function
@ 2003-03-17 10:35 Objectz <objectz@postmark.net>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 6+ messages in thread
From: Objectz @ 2003-03-17 10:35 UTC (permalink / raw)
To: pgsql-sql
Tomsaz,
Thnx a lot for the great reference It has what I need.
create function comma_aggregate(text,text) returns text as '
begin
if (length($1) > 0 ) then
return $1 || ', ' || $2;
else
return $2;
end if;
end;
' language 'plpgsql';
-- create the aggregate function
create aggregate comma (basetype=text, sfunc=comma_aggregate,
stype=text, initcond='' );
I need some help to expand on its funcitonality instead of calling
comma(col_name) to produce comma-seperated values I need to call
something like concattext(prefix, col_name, suffix) wich returns the
list in one column with the prefix and suffix aroung each entry as
specified.
This will enable me to produce something like this result
> 1 (text1) (text3)
> 2 (text2) (text5)
> 3 (text4)
With this query
Select type, concattext('(', text, ') ') from table group by type
Any help on how to convert the above comma function to receive the
prefix and suffix parameters
Regards
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Tomasz Myrta
Sent: Monday, March 17, 2003 11:16 AM
To: Objectz
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] String aggregate function
Objectz wrote:
> Hi all,
>
> I want to make an aggregate function that concatenates strings from a
> certain column into one cell in a group by clause. For example I have
> the following table :
>
> Type Text
> =================
> 1 text1
> 2 text2
> 1 text3
> 3 text4
> 2 text5
>
> I need a query that group by type and concats the text columns to
> produce something like the following :
>
> 1 text1, text3
> 2 text2, text5
> 3 text4
>
> I am aware that this can be done through recursive joins but this is
> too complicated and heavy.
>
> Any suggestions?
>
> Regards
Look at this site (Aggregate Functions):
http://www.brasileiro.net:8080/postgres/cookbook/
Regards,
Tomasz Myrta
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: String aggregate function
@ 2003-03-17 10:55 Tomasz Myrta <jasiek@klaster.net>
parent: Objectz <objectz@postmark.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tomasz Myrta @ 2003-03-17 10:55 UTC (permalink / raw)
To: Objectz <objectz@postmark.net>; +Cc: pgsql-sql
Uz.ytkownik Objectz napisa?:
> Tomsaz,
>
> Thnx a lot for the great reference It has what I need.
>
> create function comma_aggregate(text,text) returns text as '
> begin
> if (length($1) > 0 ) then
> return $1 || ', ' || $2;
> else
> return $2;
> end if;
> end;
> ' language 'plpgsql';
>
> -- create the aggregate function
>
> create aggregate comma (basetype=text, sfunc=comma_aggregate,
> stype=text, initcond='' );
>
>
> I need some help to expand on its funcitonality instead of calling
> comma(col_name) to produce comma-seperated values I need to call
> something like concattext(prefix, col_name, suffix) wich returns the
> list in one column with the prefix and suffix aroung each entry as
> specified.
>
> This will enable me to produce something like this result
>
>
>>1 (text1) (text3)
>>2 (text2) (text5)
>>3 (text4)
>
>
> With this query
> Select type, concattext('(', text, ') ') from table group by type
>
> Any help on how to convert the above comma function to receive the
> prefix and suffix parameters
>
> Regards
Change this function to separate fields only with space character
(instead of comma) and use as below:
select comma('(' || text ')') ...
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: String aggregate function
@ 2003-03-17 10:59 Tomasz Myrta <jasiek@klaster.net>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tomasz Myrta @ 2003-03-17 10:59 UTC (permalink / raw)
To: Objectz <objectz@postmark.net>; +Cc: pgsql-sql
Uz.ytkownik Tomasz Myrta napisa?:
> Change this function to separate fields only with space character
> (instead of comma) and use as below:
>
> select comma('(' || text ')') ...
Sorry:
select comma('(' || text || ')') ...
Tomasz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: String aggregate function
@ 2003-03-17 14:25 Objectz <objectz@postmark.net>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 6+ messages in thread
From: Objectz @ 2003-03-17 14:25 UTC (permalink / raw)
To: 'Tomasz Myrta' <jasiek@klaster.net>; +Cc: pgsql-sql
Didn't know this was possible.
Thnx a lot for ur support
-----Original Message-----
From: Tomasz Myrta [mailto:jasiek@klaster.net]
Sent: Monday, March 17, 2003 12:59 PM
To: Objectz
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] String aggregate function
Uz.ytkownik Tomasz Myrta napisa?:
> Change this function to separate fields only with space character
> (instead of comma) and use as below:
>
> select comma('(' || text ')') ...
Sorry:
select comma('(' || text || ')') ...
Tomasz
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-17 14:25 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-17 08:01 String aggregate function Objectz <objectz@postmark.net>
2003-03-17 09:16 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 10:35 ` Objectz <objectz@postmark.net>
2003-03-17 10:55 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 10:59 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-17 14:25 ` Objectz <objectz@postmark.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox