Обсуждение: 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