Re: Modifying SQL parser with extensions?

On 10/29/06, Matthias Luedtke <matthias-luedtke@gmx.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