pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Joe Conway <mail@joeconway.com>
To: Michael A Nachbaur <mike@nachbaur.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: "Join" on delimeter aggregate query
Date: Sat, 07 Jun 2003 18:03:50 -0700
Message-ID: <3EE28B76.9040501@joeconway.com> (raw)
In-Reply-To: <200306061003.29430.mike@nachbaur.com>
References: <200306061003.29430.mike@nachbaur.com>

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

view thread (9+ messages)  latest in thread

Message-ID: <3EE28B76.9040501@joeconway.com>
Permalink:  ../3EE28B76.9040501@joeconway.com/
Also on:    postgresql.org/message-id/3EE28B76.9040501@joeconway.com

copy link · copy postgr.es

reply

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: mail@joeconway.com, mike@nachbaur.com
  Subject: Re: "Join" on delimeter aggregate query
  In-Reply-To: <3EE28B76.9040501@joeconway.com>

* 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