Re: Modifying WHERE input conditions for a VIEW

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

For not testing this solution works amazingly well! :)

There is 3 orders of magnitude improvement in query speed, making it
suitable for our needs! Now on to the task of fixing our schema and
data.

Thank you Tom. This kind of assistance is exactly what makes PostgreSQL
great! (well, on top of being a superior product, IMHO).

--
Tony Holmes

Founder and Senior Systems Architect
Crosswinds Internet Communications Inc.