Message-ID: <3FCB9E01.6010405@klaster.net>
Date: Mon, 01 Dec 2003 21:01:05 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.5) Gecko/20031007
MIME-Version: 1.0
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: Concatenating multiple fetches into a single string
References: <024a01c3b802$d9afac40$7502a8c0@hdsc.com>
In-Reply-To: <024a01c3b802$d9afac40$7502a8c0@hdsc.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

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