Message-Id: <5.2.1.1.0.20060107154638.04237ad0@pop6.sympatico.ca>
Date: Sat, 07 Jan 2006 15:47:58 -0500
To: pgsql-sql@postgresql.org
From: Frank Bax <fbax@sympatico.ca>
Subject: Re: how to transform list to table and evaluate an
In-Reply-To: <43BFE657.7050303@fuzzy.cz>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

Today's your lucky day (I think), because I was looking for (and used) the 
aggregate function mentioned below just before reading your question.


At 11:03 AM 1/7/06, Tomas Vondra wrote:
>1) How to create a "table" in the form
>
>    documtent_id  | word_1 | word_2 | ... | word_n
>   -------------------------------------------------
>         1345     |   11   |   12   | ... |   2
>         1202     |    1   |    0   | ... |  12
>           .           .        .      .      .
>           .           .        .      .      .
>         1129     |   20   |    1   | ... |   0
>
>    from the query
>
>    SELECT docId, word, score FROM Index WHERE word IN (word_1,..,word_n)


 From section 31.10 of the 8.0 docs came this function...
  CREATE AGGREGATE array_accum (
      sfunc = array_append,
      basetype = anyelement,
      stype = anyarray,
      initcond = '{}'
  );

This query will return one row per docld.  It's not exactly the format you 
asked for, but perhaps it's a start ...

select docld,array_accum(word),array_accum(score) from index where word in 
('apples','orange') group by docld;

Then your could write two functions (beyond my capabilities):
minarray( int[] ) so you could select minarray( array_accum(score) ) /* 
apples AND orange */
maxarray( int[] ) so you could select maxarray( array_accum(score) ) /* 
apples OR orange */


>2) How to evaluate the function derived from the 'search string' on this
>    table, but this probably will work as an EXECUTE statement or
>    something like that.


Not sure what you're asking here....