From: Objectz <objectz@postmark.net>
To: pgsql-sql@postgresql.org
Subject: Re: String aggregate function
Date: Mon, 17 Mar 2003 12:35:30 +0200
Message-ID: <000c01c2ec70$f2e6f5f0$aea067d4@eg1opwxp107> (raw)
In-Reply-To: <3E75925A.5000907@klaster.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: objectz@postmark.net
Subject: Re: String aggregate function
In-Reply-To: <000c01c2ec70$f2e6f5f0$aea067d4@eg1opwxp107>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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