X-Original-To: pgsql-sql@postgresql.org
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id CAEE4474E53
	for <pgsql-sql@postgresql.org>; Mon, 17 Mar 2003 05:55:02 -0500 (EST)
Received: from klaster.net (pb150.krakow.cvx.ppp.tpnet.pl [213.76.37.150])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id D324D6A1DE; Mon, 17 Mar 2003 11:54:58 +0100 (CET)
Message-ID: <3E75A9BE.6080701@klaster.net>
Date: Mon, 17 Mar 2003 11:55:58 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.3) Gecko/20030312
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Objectz <objectz@postmark.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: String aggregate function
References: <000c01c2ec70$f2e6f5f0$aea067d4@eg1opwxp107>
In-Reply-To: <000c01c2ec70$f2e6f5f0$aea067d4@eg1opwxp107>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200303/386
X-Sequence-Number: 12425

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