pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"Join" on delimeter aggregate query
9+ messages / 5 participants
[nested] [flat]
* "Join" on delimeter aggregate query
@ 2003-06-06 17:03 Michael A Nachbaur <mike@nachbaur.com>
0 siblings, 3 replies; 9+ messages in thread
From: Michael A Nachbaur @ 2003-06-06 17:03 UTC (permalink / raw)
To: pgsql-sql
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>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-07 21:06 Eivind Kvedalen <eivindkv@ifi.uio.no>
parent: Michael A Nachbaur <mike@nachbaur.com>
2 siblings, 2 replies; 9+ messages in thread
From: Eivind Kvedalen @ 2003-06-07 21:06 UTC (permalink / raw)
To: Michael A Nachbaur <mike@nachbaur.com>; +Cc: pgsql-sql
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
--
| 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-08 01:03 Joe Conway <mail@joeconway.com>
parent: Michael A Nachbaur <mike@nachbaur.com>
2 siblings, 0 replies; 9+ messages in thread
From: Joe Conway @ 2003-06-08 01:03 UTC (permalink / raw)
To: Michael A Nachbaur <mike@nachbaur.com>; +Cc: pgsql-sql
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.org
tom@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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-08 11:37 Bruno Wolff III <bruno@wolff.to>
parent: Michael A Nachbaur <mike@nachbaur.com>
2 siblings, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2003-06-08 11:37 UTC (permalink / raw)
To: Michael A Nachbaur <mike@nachbaur.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-09 01:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Eivind Kvedalen <eivindkv@ifi.uio.no>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2003-06-09 01:05 UTC (permalink / raw)
To: Eivind Kvedalen <eivindkv@ifi.uio.no>; +Cc: Michael A Nachbaur <mike@nachbaur.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-09 08:35 Eivind Kvedalen <eivindkv@ifi.uio.no>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Eivind Kvedalen @ 2003-06-09 08:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-09 14:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Eivind Kvedalen <eivindkv@ifi.uio.no>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2003-06-09 14:06 UTC (permalink / raw)
To: Eivind Kvedalen <eivindkv@ifi.uio.no>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-09 20:08 Michael A Nachbaur <mike@nachbaur.com>
parent: Eivind Kvedalen <eivindkv@ifi.uio.no>
1 sibling, 0 replies; 9+ messages in thread
From: Michael A Nachbaur @ 2003-06-09 20:08 UTC (permalink / raw)
To: Eivind Kvedalen <eivindkv@ifi.uio.no>; +Cc: pgsql-sql
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>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: "Join" on delimeter aggregate query
@ 2003-06-10 13:58 Bruno Wolff III <bruno@wolff.to>
parent: Eivind Kvedalen <eivindkv@ifi.uio.no>
1 sibling, 0 replies; 9+ messages in thread
From: Bruno Wolff III @ 2003-06-10 13:58 UTC (permalink / raw)
To: Eivind Kvedalen <eivindkv@ifi.uio.no>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2003-06-10 13:58 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-06 17:03 "Join" on delimeter aggregate query Michael A Nachbaur <mike@nachbaur.com>
2003-06-07 21:06 ` Eivind Kvedalen <eivindkv@ifi.uio.no>
2003-06-09 01:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-09 08:35 ` Eivind Kvedalen <eivindkv@ifi.uio.no>
2003-06-09 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-06-10 13:58 ` Bruno Wolff III <bruno@wolff.to>
2003-06-09 20:08 ` Michael A Nachbaur <mike@nachbaur.com>
2003-06-08 01:03 ` Joe Conway <mail@joeconway.com>
2003-06-08 11:37 ` Bruno Wolff III <bruno@wolff.to>
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