Обсуждение: "Join" on delimeter aggregate query
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? -- Michael A Nachbaur <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.orgtom@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
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,3B | aC | 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
--
| Mail: eivindkv@ifi.uio.no | Lazy on IRC
| HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med håret
| Tlf: 22187123/93249534 | inn."
| | -- Yang Tse Lyse
On Fri, Jun 06, 2003 at 10:03:29 -0700, Michael A Nachbaur <mike@nachbaur.com> wrote: > > 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? You could write an aggregate function to concatenate email addresses with comma separators.
Eivind Kvedalen <eivindkv@ifi.uio.no> writes:
> SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a;
> 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?
This technique will work reliably as of 7.4, but it's not reliable
in existing releases. The GROUP BY will do its own sort on A, and
unless qsort() is stable on your machine (which it's not, in most
implementations) the secondary ordering by B will be destroyed.
The fix in 7.4 simply makes the planner smart enough to notice that
the sub-select's output is already adequately sorted for grouping
by A.
regards, tom lane
Eivind Kvedalen <eivindkv@ifi.uio.no> writes:
> Ok. What I actually had in mind was whether the optimizer would remove the
> ORDER BY clause completely or not,
No. If you put an ORDER BY in a subselect, I think the system should
honor it.
regards, tom lane
On Sun, 8 Jun 2003, Tom Lane wrote: > Eivind Kvedalen <eivindkv@ifi.uio.no> writes: > > SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a; > > > 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? > > This technique will work reliably as of 7.4, but it's not reliable > in existing releases. The GROUP BY will do its own sort on A, and > unless qsort() is stable on your machine (which it's not, in most > implementations) the secondary ordering by B will be destroyed. > > The fix in 7.4 simply makes the planner smart enough to notice that > the sub-select's output is already adequately sorted for grouping > by A. Ok. What I actually had in mind was whether the optimizer would remove the ORDER BY clause completely or not, as it isn't used in the top-level SELECT query, and SQL doesn't in general guarantee ordered rows back unless there's an ORDER BY in the top-level SELECT (I haven't read the SQL standard, so I might very well be wrong here). The GROUP BY sorts on A to do the grouping correctly, right? (That is, removing the duplicates from A) (Now, thinking more about this, removing the ORDER BY in the optimizer as I suggested above would effectively remove the ORDER BY in created views, right?) Eivind -- | Mail: eivindkv@ifi.uio.no | Lazy on IRC | HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med håret | Tlf: 22187123/93249534 | inn." | | -- Yang Tse Lyse
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>
On Mon, Jun 09, 2003 at 10:35:10 +0200, Eivind Kvedalen <eivindkv@ifi.uio.no> wrote: > > Ok. What I actually had in mind was whether the optimizer would remove the > ORDER BY clause completely or not, as it isn't used in the top-level > SELECT query, and SQL doesn't in general guarantee ordered rows back > unless there's an ORDER BY in the top-level SELECT (I haven't read the SQL > standard, so I might very well be wrong here). Allowing ordering in subselects is a feature (mostly for use with LIMIT) and won't be removed because it isn't a top level select.