A common use of functional indexes for Postgres and Oracle is to apply a "upper" or "lower" to a character column for case insensitive searching and ordering. For example, with the following people table:
id name -- ---------------- 1 adam 2 Troy select name from people where people='troy' # returns 0 records select name from people order by people asc # returns Troy,adam So what usually happens to fix this is a query like: select name from people where upper(people)='TROY' # returns Troy But this can kill performance, as your not using an index. No problem, add a functional index: CREATE INDEX upper_name_idx ON people ((upper(name))); Ok, great. The reason for my verbosity is I would like to see (at least as an option) that if a upper or lower functional index exists on a char field then to automagically invoke the function on the where clause or order by. I would be interested to know how others writing applications using SA targeting multiple databases are dealing with Oracle and Postgres returning different data and different ordered results than MySQL and MS-SQL depending on the case of the data in the column. From what I've concluded, there are 3 options: 1) In the domain logic, lower or upper the data before it gets to the database: a) Ugly single case for users b) Still breaks oracle and postgres for where/like without a function clause 2) Manually apply func.upper/lower to conditions/order-by's to specified columns: a) Not confirmed, but MySQL probably would not use an index b) Hard for developers to remember which columns have a functional index 3) SA Automagically applies func.upper/lower to char columns with a upper/lower function: a) Makes life easy What do you think Mike? And where is the best place to do the automagic, in the dialect or the compiler? Troy On Feb 12, 3:28 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > by "function index", we're talking expressions stuck into Index() ? > shouldnt be too hard to implement, if you submit a patch that does it > similarly to how column level defaults are done. kind of a 5-liner id > think. > > On Feb 12, 5:24 pm, "Troy" <[EMAIL PROTECTED]> wrote: > > > Is there a time frame, or an intention, for supporting function > > indexes? I would like to help out if there is no one on this. I've > > spent a few hours pouring over the sqlalchemy code and have a few > > ideas and questions if anyone is interested. > > > Thanks, > > > Troy --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
