From: Michael A Nachbaur <mike@nachbaur.com>
To: Eivind Kvedalen <eivindkv@ifi.uio.no>
Subject: Re: "Join" on delimeter aggregate query
Date: Mon, 9 Jun 2003 13:08:48 -0700
User-Agent: KMail/1.5
Cc: pgsql-sql@postgresql.org
References: <200306061003.29430.mike@nachbaur.com>
	<Pine.SOL.4.51.0306072257180.25070@tva.ifi.uio.no>
In-Reply-To: <Pine.SOL.4.51.0306072257180.25070@tva.ifi.uio.no>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200306091308.48367.mike@nachbaur.com>

Thanks very much, this helps immensely.  I've worked with functions before, 
but never aggregates.  I guess there's some more bedtime reading for me to 
look into now.

Re: sorting, this is not important to me, but I will keep the issues brought 
up by Tom Lane in mind when I use this.

On Saturday 07 June 2003 02:06 pm, Eivind Kvedalen wrote:
> Hi
>
> You can create an aggregate function to solve this. A friend of mine asked
> the same question a while ago, and I created a possible example solution
> for him, which I paste here:
>
> CREATE FUNCTION concat(varchar,varchar) RETURNS varchar
>      AS 'SELECT CASE
>            $1 WHEN \'\' THEN $2
>            ELSE $1 || \',\'|| $2
>          END AS RESULT;'
>      LANGUAGE SQL;
>
> /* DROP AGGREGATE concat(varchar); */
>
> CREATE AGGREGATE concat (
>   BASETYPE = varchar,
>   SFUNC = concat,
>   STYPE = varchar,
>   INITCOND = ''
> );
>
> /* Example code */
>
> DROP TABLE test;
> CREATE TABLE test (
>       a varchar,
>       b varchar
> );
>
> INSERT INTO test VALUES ('A', '1');
> INSERT INTO test VALUES ('A', '3');
> INSERT INTO test VALUES ('A', '2');
> INSERT INTO test VALUES ('B', 'a');
> INSERT INTO test VALUES ('C', 'b');
> INSERT INTO test VALUES ('C', 'c');
>
> SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a;
>
> /*
>
>  a | concat
> ---+---------
>  A | 1,2,3
>  B | a
>  C | b,c
>
> */
>
> The ORDER BY is included to sort the rows before they are aggregated. I'm
> not sure that this guarantees that they actually will be sorted, but maybe
> some of the postgresql hackers can confirm/deny this? I guess this isn't
> important to you, though.
>
> On Fri, 6 Jun 2003, Michael A Nachbaur wrote:
> > Hello everyone,
> >
> > I've set up PostgreSQL as the authentication / configuration database for
> > my mail server (Postfix + Courier-IMAP), and though it works beautifully,
> > I need some help on my aliases query.
> >
> > You see, define aliases in a database table as rows in a column in the
> > form of "Source" and "Target".  The problem is that one source address
> > can be delivered to multiple targets (e.g. internal mailing list, or a
> > temporary forward to another address), but postfix only processes the
> > first record returned from an SQL query.
> >
> > Postfix can deliver to multiple targets, if you separate the targets with
> > comas, like so:
> >
> > Source             Target
> > joe@bob.org   address1@bob.org,address2@bob.org,....
> >
> > What I would like to do, is something like the following (I know I'd need
> > to group the query, but you get the idea):
> >
> > Select DISTINCT(Source), JOIN(Target, ',') FROM Aliases
> >
> > Is there any way this can be done with Postfix?
>
> Eivind

-- 
Michael A Nachbaur <mike@nachbaur.com>