Re: Modifying SQL parser with extensions?

On 10/29/06, Matthias Luedtke <matthias-luedtke(at)gmx(dot)de> 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