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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Importance: Normal
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