On 1 April 2016 at 11:33, arnaud gaboury <arnaud.gabo...@gmail.com> wrote:
> I have one table email.mail.mailusers : > > Table "email.mailusers" > Column | Type | > Modifiers | Storage > | Stats target | Description > > -------------+--------------------------+---------------------------------------------+-------- > --+--------------+------------- > username | text | not > null | extende > d | | > password | text | not > null | extende > d | | > domain_name | text | not null default ' > thetradinghall.com'::text | extende > d | | > created | timestamp with time zone | not null default > now() | plain > | | > Indexes: > "mailusers_pkey" PRIMARY KEY, btree (username, domain_name) > Foreign-key constraints: > "mailusers_domain_fk" FOREIGN KEY (domain_name) REFERENCES > email.domainlist(domain_name) > > username | password | domain_name | created > > ----------------+--------------------------------------------------------------+--------------- > arnaud.gaboury | XXXXYYYYYY | thetradinghall.com | 2016-02-04 > 09:48:58.834774+01 > > admin |XXXYYYYYY | thetradinghall.com | 2016-03-29 > 09:58:14.599743+02 > > postmaster | XXXYYYYYY | thetradinghall.com | 2016-03-31 > 16:36:18.96176+02 > > ---------------------------------------------------------- > > I have one view email.mail_dir : > > View "email.mail_dir" > Column | Type | Modifiers | Storage | Description > ----------+------+-----------+----------+------------- > home_dir | text | | extended | > View definition: > SELECT ((mailusers.domain_name || '/'::text) || mailusers.username) || > '/'::text AS home_dir > FROM email.mailusers; > > home_dir > ------------------------------------ > thetradinghall.com/arnaud.gaboury/ > thetradinghall.com/admin/ > thetradinghall.com/postmaster/ > > --------------------------------------------- > > What I am trying to do: I want the <home_dir> be returned for <username> u. > 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. > But it returns: > home_dir > ------------------------------------ > thetradinghall.com/arnaud.gaboury/ > thetradinghall.com/admin/ > thetradinghall.com/postmaster/ > (3 rows) > You would have seen the problem, if you had used * instead d.homedir. And there is no solution to your problem in the given circumstances. You need the usename field in the view as well and then you can: SELECT d.home_dir FROM email.mail_dir d, email.mailusers u WHERE u.username='arnaud.gaboury' and u.usename=d.usename; But it's completely unnecessary. You can simply redefine the view. SELECT *,((mailusers.domain_name || '/'::text) || mailusers.username) || '/'::text AS home_dir FROM email.mailusers; 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. Regards, Sándor
