Re: Modifying WHERE input conditions for a VIEW

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