Re: Modifying WHERE input conditions for a VIEW

Tony Holmes <tony(at)crosswinds(dot)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