* David Jarvis (thangalin@xxxxxxxxx) wrote: > I was hoping to eliminate this part of the query: > (cast(extract( YEAR FROM m.taken ) + greatest( -1 * > sign( > (extract( YEAR FROM m.taken )||'-12-31')::date - > (extract( YEAR FROM m.taken )||'-01-01')::date ), 0 > ) AS text)||'-12-31')::date > > That uses functions to create the dates, which is definitely the problem. [...] > The greatest() expression reduces to either the current year (year + 0) or > the next year (year + 1) by taking the sign of the difference in start/end > days. This allows me to derive an end date, such as: > > Dec 22, 1900 to Mar 22, 1901 Something in here really smells fishy to me. Those extract's above are working on values which are from the table.. Why aren't you using these functions to figure out how to construct the actual dates based on the values provided by the *user*..? Looking at your screenshot, I think you need to take those two date values that the user provides, make them into actual dates (maybe you need a CASE statement or something similar, that shouldn't be that hard, and PG should just run that whole bit once, since to PG's point of view, it's all constants), and then use those dates to query the tables. Also, you're trying to do constraint_exclusion, but have you made sure that it's turned on? And have you made sure that those constraints are really the right ones and that they make sense? You're using a bunch of extract()'s there too, why not just specify a CHECK constraint on the date ranges which are allowed in the table..? Maybe I've misunderstood the whole point here, but I don't think so. Thanks, Stephen
Attachment:
signature.asc
Description: Digital signature
