From: Tomasz Myrta <jasiek@klaster.net>
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: Concatenating multiple fetches into a single string
Date: Mon, 01 Dec 2003 21:01:05 +0100
Message-ID: <3FCB9E01.6010405@klaster.net> (raw)
In-Reply-To: <024a01c3b802$d9afac40$7502a8c0@hdsc.com>
References: <024a01c3b802$d9afac40$7502a8c0@hdsc.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, sgnerd@yahoo.com.sg
Subject: Re: Concatenating multiple fetches into a single string
In-Reply-To: <3FCB9E01.6010405@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox