Re: Design: Escort info from WHERE clause to executor?

[Heikki Linnakangas <heikki(at)enterprisedb(dot)com>]

peter(dot)trautmeier(at)gmx(dot)de wrote:
> Von: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
>> peter(dot)trautmeier(at)gmx(dot)de wrote:
>>> To sum up, I am looking for a (decently efficient) scheme that is able
>> to
>>> (1) pass arbitrary conditional expressions from WHERE to the executor in
>> a structure preserving way. 
>>> (2) annotate arbitrary expressions with weights that survive on its way
>> from the parser to the executor.
>>> (3) access the logical value of particular subexpressions.
>>>
>>> I have some basic ideas how at least some of the requirements might be
>> achieved. But as I am not totally satisfied with my ideas I hope you can
>> provide me with some fresh input.
>>
>> Why? What are you trying to achieve?
> 
> I am implementing a technique that sorts a result set according to weight annotations in the WHERE.
> 
> The query
> 
> SELECT * FROM cars 
> WHERE (cdChanger=1){2} 
>    OR (mp3player=1){1} 
> 
> would be sorted according to partial conditions that hold.

You could do that like this, with no need to hack the backend:

SELECT * FROM cars
WHERE (cdChanger=1)
   OR (mp3player=1)
ORDER BY (CASE WHEN cdchanger=1 THEN 2 ELSE 0 END) +
         (CASE WHEN mp3player=1 THEN 1 ELSE 0 END) DESC;

Or a bit shorter version, exploiting the cast from boolean to int:

SELECT * FROM cars
WHERE (cdChanger=1)
   OR (mp3player=1)
ORDER BY (cdchanger=1)*2 + (mp3player=1)*1 DESC;

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com