On 10/29/06, Matthias Luedtke <[EMAIL PROTECTED]> wrote:
Alvaro Herrera wrote:
>> In fact, parsing this SQL dialect would just be the first step, as the
>> annotations within the query induce an ordering of the result set.
>
> Huh, what is this supposed to be able to do that you can't do with the
> already existing ORDER BY clause?
Basically, conditional statements are annotated with integers that
represent weights, like
(...)WHERE (foo = 'a')[42] OR (bar = 'b')[20]
In the result set those entries that fulfill both conditions yield score
62, i.e. 42+20, and are ranked top, whereas entries that fulfill only
one of the conditions yield scores 42 and 20 respectively and are
therefore ranked lower.
So, basically you're giving sets of three parameters:
column value, your value, score for that column
and your query should return score for the sum of all
those values. I'll assume you only use '=' -- if you use
other conditions, feel free to modify!
First, your example data:
qnex=# CREATE TABLE blah (foo text, bar text);
qnex=# INSERT INTO blah VALUES ('a','a');
qnex=# INSERT INTO blah VALUES ('a','b');
qnex=# INSERT INTO blah VALUES ('b','b');
qnex=# INSERT INTO blah VALUES ('c','c');
Second, a user defined scorecounter:
CREATE OR REPLACE FUNCTION scorecounter(colval text[], yourval text[],
score int[]) RETURNS int AS $$
DECLARE
i int DEFAULT 1;
retscore int DEFAULT 0;
BEGIN
WHILE score[i] IS NOT NULL
LOOP
IF colval[i] = yourval[i] THEN
retscore := retscore + score[i];
END IF;
i := i+1;
END LOOP;
RETURN retscore;
END $$ LANGUAGE PLpgSQL;
I used PL/pgSQL but you may prefer to user perl instead -- the idea
stays the same. And now for the grand finalle:
SELECT *, scorecounter(ARRAY[foo,bar], ARRAY['a','b'], ARRAY[42,20]) from blah;
foo | bar | scorecounter
-----+-----+--------------
a | a | 42
a | b | 62
b | b | 20
c | c | 0
SELECT * FROM blah ORDER BY scorecounter(ARRAY[foo,bar],
ARRAY['a','b'], ARRAY[42,20]) DESC;
foo | bar
-----+-----
a | b
a | a
b | b
c | c
Note that you should add some error checking into the function,
and if you prefer, you may user other syntax for arrays, I used
ARRAY[...] because it felt self explanatory.
Regards,
Dawid
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
