Re: Modifying WHERE input conditions for a VIEW

On +Mar 30, Tom Lane wrote:
> > 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?

Yes, there is only 2. I am dealing with legacy issues here.

@foo.com is a "large" community site with domains "overlayed" (that best
describes it). In the case of email we are using virtual delivery to give
more email addresses for individual domains.

I now forsee the benefit of a single table, but moving the old foo.com
domain is painful at best. (foo.com has 1.5 million users and a lot of
special case code).

The new domain_users table will be all domains and users that are newly
registered.

> 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).

Yes, this works and is quite slow :/

Speed is a bit of an issue since it will be dealing with email.

One approach I had tried (my SQL background isn't the strongest) is
creation of a view (which works) that returns the data required
from the table in the format the mailserver wants it. That works
wonderfully.

I then attempted to use a SELECT rule on that view. It looked like
this (the view was imap_lookup):

CREATE RULE imap_lookup AS
    ON SELECT TO imap_lookup
    WHERE strpos(username, '@foo.com') DO INSTEAD
        SELECT uid, username, password, active
        FROM foo_users
        WHERE username=rtrim(username,'@foo.com');

Thus, redirecting only the queries for @foo.com to the correct
table.

I kept running into "ON SELECT rule may not use OLD".

> 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.

I agree, but I'm kinda bound to what we have at the moment. Changing it
is in the plans, but more long term.

--
Tony Holmes

Founder and Senior Systems Architect
Crosswinds Internet Communications Inc.