Обсуждение: Select question

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@test.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@test.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@test.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

Woops, I wasn't careful enough when I wrote that email, sorry. The
results showed my real domains instead of 'test.com'. I had different
domains in the test and real DBs.

Madison Kelly wrote:
>        email_file
> -------------------------
>  feneon.com/mkelly/inbox

and

>         email_file
> --------------------------
>  alteeve.com/mkelly/inbox

*sigh*

   Should have shown:

       email_file
-----------------------
  test.com/mkelly/inbox

I'll go get a coffee and wake up some more. :)

Madi

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@test.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@test.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@test.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@postgresql.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@test.com';
-=-=-=-

   To return:

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

   Perfect! :)

Madi

On 8/30/07, Madison Kelly <linux@alteeve.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@test.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

Merlin Moncure wrote:
> 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

   Hiya,

Nope, wasn't me, but I was indeed able to solve the problem with a few
(I posted the details in a follow up). It was pretty similar to your
suggestion, so you were certainly onto something. :)

Madi