Re: Modifying WHERE input conditions for a VIEW

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

This isn't going to work; we do not support WHERE conditions in ON
SELECT rules at all.  (There is no difference between ON SELECT rules
and views, actually.)

>> My advice is to change the schema.

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

You might be able to make it work efficiently with a functional index.
To meet the syntactic limitations of functional indexes, you'd need to
define a function "addfoo(text) returns text" that returns the input
with "@foo.com" appended.  (Be sure to mark it strict and immutable.)
Now you can create an index on addfoo(username) for foo_users, and
then your view becomes

    CREATE VIEW all_users as
        SELECT uid, addfoo(username) AS username, password, active
        FROM foo_users
        UNION ALL
        SELECT uid, username, password, active
        FROM domain_users

I haven't tested, but I believe 7.3 will optimize this fairly decently
when the query is "SELECT ... FROM all_users WHERE username = something'.

            regards, tom lane