From sgnerd@yahoo.com.sg Mon Dec  1 12:04:20 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id A2417D1C526
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  1 Dec 2003 12:04:20 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 90996-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  1 Dec 2003 08:03:52 -0400 (AST)
Received: from smtp107.mail.sc5.yahoo.com (smtp107.mail.sc5.yahoo.com
	[66.163.169.227])
	by svr1.postgresql.org (Postfix) with SMTP id 003EDD1B8B6
	for <pgsql-sql@postgresql.org>; Mon,  1 Dec 2003 08:03:48 -0400 (AST)
Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67)
	(sgnerd@203.196.190.138 with login)
	by smtp-v1.mail.vip.sc5.yahoo.com with SMTP; 1 Dec 2003 12:03:49 -0000
Message-ID: <024a01c3b802$d9afac40$7502a8c0@hdsc.com>
Reply-To: "Kumar" <sgnerd@yahoo.com.sg>
From: "Kumar" <sgnerd@yahoo.com.sg>
To: "psql" <pgsql-sql@postgresql.org>
Subject: Concatenating multiple fetches into a single string
Date: Mon, 1 Dec 2003 17:31:07 +0530
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0247_01C3B830.E73977F0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_30_40
X-Spam-Level: 
X-Archive-Number: 200312/2
X-Sequence-Number: 16168

This is a multi-part message in MIME format.

------=_NextPart_000_0247_01C3B830.E73977F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Friends,

I am doing a migration from SQL server to Postgres SQL. A simple select fet=
ches the following names.

select full_name FROM project_members where project_members.project_role_id=
 in (' + @p_res_ids + ') ;=20

Let us say if the results are=20

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

I need to send the out put as David,Postgres,Plsql -- one string, concatena=
tes the fetches from multiple rows. This was handled in SQL server with a c=
ursor.

Can this be handled only by sql manipulation or need to use cursors. If i u=
se cursor in postgres, what is the equivalent of MS SQL Statement=20
WHILE @@FETCH_STATUS =3D 0.

please shed some light pls.

------=_NextPart_000_0247_01C3B830.E73977F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY style=3D"COLOR: #0000ff; FONT-FAMILY: Arial" bgColor=3D#ffffff>
<DIV><FONT size=3D2>Dear Friends,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I am doing a migration from SQL server to Postgres SQL.=
 A=20
simple select fetches the following names.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>select full_name FROM project_members where=20
project_members.project_role_id in (' + @p_res_ids + ') ; </FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Let us say if the results are </FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>full_name</FONT></DIV>
<DIV><FONT size=3D2>---------------</FONT></DIV>
<DIV><FONT size=3D2>David</FONT></DIV>
<DIV><FONT size=3D2>Postgres</FONT></DIV>
<DIV><FONT size=3D2>plpgsql</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>I need to send the out put as David,Postgres,Plsql -- o=
ne=20
string, concatenates the fetches from multiple rows. This was handled in SQ=
L=20
server with a cursor.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Can this be handled only by sql manipulation or need to=
 use=20
cursors. If i use cursor in postgres, what is the equivalent of MS SQL Stat=
ement=20
</FONT></DIV>
<DIV><FONT size=3D2>WHILE @@FETCH_STATUS =3D 0.</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>please shed some light pls.</FONT></DIV>
<P></P></BODY></HTML>

------=_NextPart_000_0247_01C3B830.E73977F0--




From jasiek@klaster.net Mon Dec  1 20:02:07 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id EB58BD1CCD0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  1 Dec 2003 20:02:07 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 71614-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  1 Dec 2003 16:01:38 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id 5D4EAD1DA92
	for <pgsql-sql@postgresql.org>; Mon,  1 Dec 2003 16:01:20 -0400 (AST)
Received: from klaster.net (core-1.citynet.pl [80.48.135.69])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 0AABA2B40F; Mon,  1 Dec 2003 21:00:41 +0100 (CET)
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
X-Accept-Language: pl, en-us, en
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
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/3
X-Sequence-Number: 16169

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




From sgnerd@yahoo.com.sg Tue Dec  2 04:54:22 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 3C789D1B554
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Dec 2003 04:54:22 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 52742-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Dec 2003 00:53:50 -0400 (AST)
Received: from smtp107.mail.sc5.yahoo.com (smtp107.mail.sc5.yahoo.com
	[66.163.169.227])
	by svr1.postgresql.org (Postfix) with SMTP id 886CBD1B590
	for <pgsql-sql@postgresql.org>; Tue,  2 Dec 2003 00:53:49 -0400 (AST)
Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67)
	(sgnerd@203.196.190.138 with login)
	by smtp-v1.mail.vip.sc5.yahoo.com with SMTP; 2 Dec 2003 04:53:47 -0000
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
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/7
X-Sequence-Number: 16173

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




From jasiek@klaster.net Tue Dec  2 06:07:43 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id C3D4ED1B4A7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Dec 2003 06:07:43 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 57119-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Dec 2003 02:07:12 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id DC2A7D1B4B7
	for <pgsql-sql@postgresql.org>; Tue,  2 Dec 2003 02:07:11 -0400 (AST)
Received: from klaster.net (core-1.citynet.pl [80.48.135.69])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id C0B812B400; Tue,  2 Dec 2003 07:07:10 +0100 (CET)
Message-ID: <3FCC2C2E.2050106@klaster.net>
Date: Tue, 02 Dec 2003 07:07:42 +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
X-Accept-Language: pl, en-us, en
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>
	<3FCB9E01.6010405@klaster.net>
	<00cc01c3b88f$f14f3540$7502a8c0@hdsc.com>
In-Reply-To: <00cc01c3b88f$f14f3540$7502a8c0@hdsc.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200312/9
X-Sequence-Number: 16175

Dnia 2003-12-02 05:51, Użytkownik Kumar napisał:
> 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'

Where is the problem?
It's an aggregate function - you use it like the other ones:

select comma(full_name) from...

There is one problem with this function - strings order is unexpectable, 
but you can always sort them in subselect before using this function:

select comma(full_name) from
(select full_name from .... order by full_name) X;

Regards,
Tomasz Myrta