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 77017EFFB8 for ; Sat, 7 Jun 2003 21:06:50 +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 73962-07 for ; Sat, 7 Jun 2003 21:06:39 +0000 (GMT) Received: from pat.uio.no (pat.uio.no [129.240.130.16]) by svr1.postgresql.org (Postfix) with ESMTP id 32C26EFFA7 for ; Sat, 7 Jun 2003 21:06:39 +0000 (GMT) Received: from mail-mx2.uio.no ([129.240.10.30]) by pat.uio.no with esmtp (Exim 2.12 #7) id 19Oktc-0003Ck-00; Sat, 7 Jun 2003 23:06:40 +0200 Received: from [129.240.130.16] (helo=pat.uio.no) by mail-mx2.uio.no with esmtp (Exim 4.14) id 19OktZ-00028z-Ix; Sat, 07 Jun 2003 23:06:37 +0200 Received: from tva.ifi.uio.no ([129.240.65.202]) by pat.uio.no with esmtp (Exim 2.12 #7) id 19OktZ-0003CS-00; Sat, 7 Jun 2003 23:06:37 +0200 Received: from localhost (eivindkv@localhost) by tva.ifi.uio.no ; Sat, 7 Jun 2003 23:06:36 +0200 (MEST) Date: Sat, 7 Jun 2003 23:06:36 +0200 (MEST) From: Eivind Kvedalen To: Michael A Nachbaur Cc: pgsql-sql@postgresql.org Subject: Re: "Join" on delimeter aggregate query In-Reply-To: <200306061003.29430.mike@nachbaur.com> Message-ID: References: <200306061003.29430.mike@nachbaur.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: QUOTED-PRINTABLE X-MailScanner-Information: This message has been scanned for viruses/spam. Contact postmaster@uio.no if you have questions about this scanning. X-UiO-MailScanner: No virus found X-Archive-Number: 200306/54 X-Sequence-Number: 13812 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 =3D varchar, SFUNC =3D concat, STYPE =3D varchar, INITCOND =3D '' ); /* 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 fo= rm 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 --=20 | Mail: eivindkv@ifi.uio.no | Lazy on IRC | HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med h=E5ret | Tlf: 22187123/93249534 | inn." | | -- Yang Tse Lyse