PostgreSQL SELECT question

[Dave Cramer]

Madison,

Try looking up views in postgresql, or rules

Dave
On 30-Aug-07, at 12:19 PM, 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-J0of1frlU80 at public.gmane.org').
>
>   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-J0of1frlU80 at public.gmane.org';
>
>   Which returns:
>
>       email_file
> -----------------------
>  test.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-J0of1frlU80 at public.gmane.org';
>
>   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
> -----------------------
>  test.com/mkelly/inbox
>
>
>   I hope I got the question across well enough. :)
>
>   Thanks all!
>
> Madi
>
> --
> The Toronto Linux Users Group.      Meetings: http://gtalug.org/
> TLUG requests: Linux topics, No HTML, wrap text below 80 columns
> How to UNSUBSCRIBE: http://gtalug.org/wiki/Mailing_lists

--
The Toronto Linux Users Group.      Meetings: http://gtalug.org/
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://gtalug.org/wiki/Mailing_lists