Tony Holmes <tony@crosswinds.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
