On Fri, Apr 1, 2016 at 3:33 PM, Sándor Daku <daku.san...@gmail.com> wrote:
> > 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. > > > 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. > > 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 > > > -- google.com/+arnaudgabourygabx <https://plus.google.com/_/notifications/emlink?emr=05814804238976922326&emid=CKiv-v6PvboCFcfoQgod6msAAA&path=%2F116159236040461325607%2Fop%2Fu&dt=1383086841306&ub=50>
