Solved! Was: Re: Select question

Madison Kelly 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'
> AS
> email_file
> FROM
> users
> WHERE
> usr_email='mkelly(at)test(dot)com';
>
> Which returns:
>
> email_file
> -------------------------
> feneon.com/mkelly/inbox
>
> Now I want to move to a more complex database where the email name
> comes from 'users' -> 'usr_email' (ie: 'mkelly') and the domain suffix
> comes from 'domains' -> 'dom_name' (ie: 'test.com').
>
> 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.
>
> So, my question,
>
> Can I create a 'virtual table' table (or some such) that would take
> something like?:
>
> SELECT email_file FROM virtual_table WHERE email_addy='mkelly(at)test(dot)com';
>
> Where the email_addy can be split to create this query:
>
> SELECT
> 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';
>
> Which would still return:
>
> email_file
> --------------------------
> alteeve.com/mkelly/inbox
>
> I hope I got the question across well enough. :)
>
> Thanks all!
>
> Madi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

I got the answer from an Ian Peterson from the GTALUG. Thought I'd post
the answer here, "for the record".

-=-=-=-
CREATE VIEW
email_file
AS SELECT
u.usr_email || '@' || d.dom_name
AS
email, d.dom_name || '/' || u.usr_email || '/inbox'
AS
file
FROM
users u
JOIN
domains d
ON
u.usr_dom_id=d.dom_id;
-=-=-=-

Which allows the query:

-=-=-=-
SELECT file FROM email_file WHERE email='mkelly(at)test(dot)com';
-=-=-=-

To return:

-=-=-=-
file
-----------------------
test.com/mkelly/inbox
-=-=-=-

Perfect! :)

Madi