[EMAIL PROTECTED] wrote:
> Von: Heikki Linnakangas <[EMAIL PROTECTED]>
>> [EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
