From: Michael A Nachbaur <mike@nachbaur.com>
To: Eivind Kvedalen <eivindkv@ifi.uio.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: "Join" on delimeter aggregate query
Date: Mon, 9 Jun 2003 13:08:48 -0700
Message-ID: <200306091308.48367.mike@nachbaur.com> (raw)
In-Reply-To: <Pine.SOL.4.51.0306072257180.25070@tva.ifi.uio.no>
References: <200306061003.29430.mike@nachbaur.com>
<Pine.SOL.4.51.0306072257180.25070@tva.ifi.uio.no>
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>
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: mike@nachbaur.com, eivindkv@ifi.uio.no
Subject: Re: "Join" on delimeter aggregate query
In-Reply-To: <200306091308.48367.mike@nachbaur.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