Re: 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