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
> > [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED],....
> >
> > 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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
