Re: Problem with index in OR'd expression

[postgresql(dot)org(at)tgice(dot)com]

Tom Lane wrote:
> Well, you could update --- 8.2 contains code to recognize that the IS
> NULL expression is constant, but prior releases do not.

That's excellent to hear -- I'd missed that in my perusing of the 
changelogs between 8.0.x and 8.2.  That does give me one more reason to 
upgrade.  It appears I did not mention what version I was running -- 
sorry for that, though you guessed it was < 8.2.  It's actually 8.0.x.

> However, if you're hoping to do this:
>
> >    ((vC1 IS NULL) OR (C1 = vC1)) AND
> >    ((vC2 IS NULL) OR (C2 = vC2)) ...
>
> you're still gonna lose because those are variables not constants ...

Well, that *is* what I'm hoping to do.  I understand how (0 IS NULL) is 
different from (variable IS NULL), but isn't it reasonable to expect 
that PG could evaluate that expression only once (knowing that the 
variable couldn't change during the course of the query execution) and 
then treat that expression as constant?  I appreciate that you're saying 
that it won't work even in 8.2, but what I'm getting at is would it be 
possible to add it in the future?

As I mentioned, I'm pretty sure that that must be what MSSQL (6.5, 7, 
2000 and 2005 [all of which I've had some experience with]) seem to be 
doing.

Now failing all of this, does any one have a better idea for what I'm 
trying to do?  A simple syntax for optionally including WHERE criteria 
depending on the null-ness of variables (w/o having to go to dynamic 
execution)?

Thanks for your reply Tom.

jl