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