X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 74923F0079
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  8 Jun 2003 01:05:12 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 05878-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  8 Jun 2003 01:04:59 +0000 (GMT)
Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86])
	by svr1.postgresql.org (Postfix) with ESMTP id 0BA04F00B7
	for <pgsql-sql@postgresql.org>; Sun,  8 Jun 2003 01:04:48 +0000 (GMT)
Received: from [192.168.5.3] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 4.0.6)
	with ESMTP-TLS id 1790277; Sat, 07 Jun 2003 17:40:56 -0700
Message-ID: <3EE28B76.9040501@joeconway.com>
Date: Sat, 07 Jun 2003 18:03:50 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Michael A Nachbaur <mike@nachbaur.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: "Join" on delimeter aggregate query
References: <200306061003.29430.mike@nachbaur.com>
In-Reply-To: <200306061003.29430.mike@nachbaur.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200306/51
X-Sequence-Number: 13809

Michael A Nachbaur wrote:
> Source             Target
> joe@bob.org   address1@bob.org,address2@bob.org,....
> 

It isn't clear from your description what you want (to me, anyway), but 
I'd guess something like this:

regression=# select * from src2tgt;
    source    |      target
-------------+------------------
  joe@bob.org | address1@bob.org
  joe@bob.org | address2@bob.org
  tom@bob.org | address3@bob.org
  tom@bob.org | address4@bob.org
(4 rows)

create or replace function mylist(text,text) returns text as '
declare
   result text;
begin
   if $1 = '''' then
     result := $2;
   else
     result := $1 || '','' || $2;
   end if;
   return result;
end;
' language 'plpgsql';

create aggregate tgtlist
(
   BASETYPE = text,
   SFUNC = mylist,
   STYPE = text,
   INITCOND = ''
);

regression=# select source, tgtlist(target) from src2tgt group by source;
    source    |              tgtlist
-------------+-----------------------------------
  joe@bob.org | address1@bob.org,address2@bob.org
  tom@bob.org | address3@bob.org,address4@bob.org
(2 rows)

HTH,

Joe