Skip site navigation (1) Skip section navigation (2)

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

From: "Dawid Kuroczko" <qnex42(at)gmail(dot)com>
To: "peter(dot)trautmeier(at)gmx(dot)de" <peter(dot)trautmeier(at)gmx(dot)de>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Design: Escort info from WHERE clause to executor?
Date: 2007-07-25 13:55:53
Message-ID: 758d5e7f0707250655v6a7827d7l2816d7ecc2aca6ab@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Gregory StarkDate: 2007-07-25 14:04:08
Subject: Re: Design: Escort info from WHERE clause to executor?
Previous:From: Heikki LinnakangasDate: 2007-07-25 13:51:38
Subject: Re: Design: Escort info from WHERE clause to executor?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group