Re: Modifying WHERE input conditions for a VIEW

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