From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tony Holmes <tony(at)crosswinds(dot)net> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Modifying WHERE input conditions for a VIEW |
Date: | 2003-03-30 19:42:47 |
Message-ID: | 5199.1049053367@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Tony Holmes <tony(at)crosswinds(dot)net> writes:
> I have 2 tables that need to be selected from, depending on the input
> WHERE condition.
> Here is their very simplified schema:
> table foo_users (
> uid serial,
> username character varying(16), -- No @domainname.com
> password character varying(16),
> active boolean default 't'
> );
> table domain_users (
> uid serial,
> username character varying(255), -- Includes @domainname.com
> password character varying(16),
> active boolean default 't'
> )
> What I want to accomplish is, if the WHERE clause matches @foo.com
> I want to select from foo_users, removing @foo.com, otherwise, return
> the data from domain_users.
Um, are there really only two tables? Or are you using "foo" to imply
that you have a bunch of domains xxx each with its own xxx_users table?
You could imagine using a view like
CREATE VIEW all_users as
SELECT uid, username || '@foo.com', password, active
FROM foo_users
UNION ALL
SELECT uid, username, password, active
FROM domain_users
and then selecting from this view. It won't be real efficient though,
and it won't scale conveniently if there are many per-domain tables
(especially if they come and go).
My advice is to change the schema. Get rid of foo_users and store all
the users in the single domain_users table, with @domainname.com for
everyone. It's false economy to avoid storing the @domainname.com, at
least if your primary traffic is queries that include it.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Holmes | 2003-03-30 20:15:47 | Re: Modifying WHERE input conditions for a VIEW |
Previous Message | Tom Lane | 2003-03-30 19:24:58 | Re: Index behavior question |