Message-ID: <00cc01c3b88f$f14f3540$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "Tomasz Myrta" <jasiek@klaster.net>
Cc: "psql" <pgsql-sql@postgresql.org>
References: <024a01c3b802$d9afac40$7502a8c0@hdsc.com>
	<3FCB9E01.6010405@klaster.net>
Subject: Re: Concatenating multiple fetches into a single string
Date: Tue, 2 Dec 2003 10:21:14 +0530
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-2"
Content-Transfer-Encoding: 8bit

Thanks for your reply.

But how to use this comma_aggregate( ) function to concatenate the fetched
columns values from a select statement. In my example my select stmt fetches
the following 3 rows. How can I use this function to concatenate them.

Select full_name FROM project_members where project_members.project_role_id
in ( ' x,y,z ') ;

 full_name
 ---------------
 David
 Postgres
 plpgsql

Expected return string is - 'David,Postgres,Plsql'

Regards
Kumar
----- Original Message ----- 
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Kumar" <sgnerd@yahoo.com.sg>
Cc: "psql" <pgsql-sql@postgresql.org>
Sent: Tuesday, December 02, 2003 1:31 AM
Subject: Re: [SQL] Concatenating multiple fetches into a single string


> Dnia 2003-12-01 13:01, Użytkownik Kumar napisał:
> > Dear Friends,
> >
> > I am doing a migration from SQL server to Postgres SQL. A simple select
> > fetches the following names.
> >
> > select full_name FROM project_members where
> > project_members.project_role_id in (' + @p_res_ids + ') ;
> >
> > Let us say if the results are
> >
> > full_name
> > ---------------
> > David
> > Postgres
> > plpgsql
> >
> > I need to send the out put as David,Postgres,Plsql -- one string,
> > concatenates the fetches from multiple rows. This was handled in SQL
> > server with a cursor.
>
> I can't find simpler function (if I remember well, there is such one),
> so there is my version of aggregate function you need:
>
>
> create or replace function comma_aggregate(varchar,varchar) returns
> varchar as '
> begin
>   if length($1)>0 and length($2)>0 then
>       return $1 || '', '' || $2;
>   elsif length($2)>0 then
>     return $2;
>   end if;
>   return $1;
> end;
> ' language 'plpgsql';
>
> drop aggregate comma(varchar) cascade;
> create aggregate comma (basetype=varchar, sfunc=comma_aggregate,
> stype=varchar, initcond='' );
>
> Regards,
> Tomasz Myrta
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly