Re: Select question

On 8/30/07, Madison Kelly <linux(at)alteeve(dot)com> wrote:
> Hi all,
>
> I am pretty sure I've done this before, but I am drawing a blank on
> how I did it or even what commands I need. Missing the later makes it
> hard to search. :P
>
> I've got Postfix working using PostgreSQL as the backend on a small,
> simple test database where I have a simple table called 'users' with a
> column called 'usr_email' which holds, surprisingly, the user's email
> address (ie: 'mkelly(at)test(dot)com').
>
> To tell Postfix where the user's email inbox is (to write incoming
> email to) I tell it to do this query:
>
> SELECT
> substring(usr_email FROM '@(.*)')||'/'||substring(usr_email FROM
> '(.*)@')||'/inbox'

> The problem is, I am limited to how I can tell Postfix to generate
> the query. Specifically, I can't (or don't know how to) tell Postfix to
> create a join or split the email address. I can only tell Postfix what
> table to query, what the SELECT field to use, and what column to do the
> WHERE on.

I seem to recall giving out a query about that in the IRC channel a
while back...so if you got it from me, now I'll attempt to finish the
job :-).

If you can get postfix to look at a view, maybe you could
CREATE VIEW email_v AS
SELECT
usr_email, dom_name,
b.dom_name||'/'||a.usr_email||'/inbox' AS email_file
FROM users a, domains b
WHERE
a.usr_dom_id=b.dom_id;
AND a.usr_email='mkelly'
AND b.dom_name='test.com';

and just
select * from email_v where usr_email = 'mkelly' and dom_name = 'test.com';

merlin