peter.trautmeier@gmx.de wrote:
> Von: Heikki Linnakangas <heikki@enterprisedb.com>
>> peter.trautmeier@gmx.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
