One of many difficulties with computers that they do what you say them to do, not what you think or you think you are saying. :) Lets see: SELECT d.home_dir FROM email.mail_dir d, email.mailusers u <- make a join between mail_dir and mailusers = join every(!) record from the first table with every(!) record from the second table WHERE u.username='arnaud.gaboury'; <- but I need only those from the joined records where the username is arnaud.gaboury
And there, you have it.
You can simply redefine the view.
SELECT *,((mailusers.domain_name || '/'::text) || mailusers.username) || '/'::text AS home_dir FROM email.mailusers;
Thank you so much. This way I get all needed info in one view.
I even added a new column in my view to catch the email address:
SELECT *,((mailusers.domain_name || '/'::text) || mailusers.username) || '/'::text AS home_dir, username||'@'||domain_name AS email_address FROM email.mailusers;
Now, I am wondering if there is a best practice between :
Notice the * after the SELECT statement. So you have all the data plus the homedir.
You can leave out the whole view thing and incorporate the home_dir expression right into your select. Or you can write a function which makes this to you with the usename as argument.