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

On 7/25/07, peter(dot)trautmeier(at)gmx(dot)de <peter(dot)trautmeier(at)gmx(dot)de> wrote:
> >
> > 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.
>
> Cars that have both a CD changer AND a MP3 player get a weight of 3, i.e. (2+1).
> Cars that only have a CD changer get a weight of 2.
> Cars that only have a MP3 player get a weight of 1.

Hmm, any particular reason why not doing it this way: ?

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;

...perhaps wrapping the CASE into something like:
CREATE FUNCTION weight_if(boolean,int) RETURNS int AS $$ SELECT CASE
WHEN $1 THEN $2 ELSE 0 END $$ IMMUTABLE LANGUAGE SQL;

...and using it like:

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

Regards,
Dawid