Skip site navigation (1) Skip section navigation (2)

Solved! Was: Re: Select question

From: Madison Kelly <linux(at)alteeve(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Solved! Was: Re: Select question
Date: 2007-08-30 18:35:21
Message-ID: 46D70DE9.8010601@alteeve.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Tom LaneDate: 2007-08-30 18:43:06
Subject: Re: Out of shared memory (locks per process) using table-inheritance style partitioning
Previous:From: Andrew SullivanDate: 2007-08-30 18:28:03
Subject: Re: date of next Version 8.2 release

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group