Re: String aggregate function - Mailing list pgsql-sql

From Objectz
Subject Re: String aggregate function
Date
Msg-id 000c01c2ec70$f2e6f5f0$aea067d4@eg1opwxp107
Whole thread Raw
In response to Re: String aggregate function  (Tomasz Myrta <jasiek@klaster.net>)
Responses Re: String aggregate function
List 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




pgsql-sql by date:

Previous
From: Achilleus Mantzios
Date:
Subject: Re: How to compare dates?
Next
From: Tomasz Myrta
Date:
Subject: Re: String aggregate function