From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: how to transform list to table and evaluate an
Date: Sat, 07 Jan 2006 15:47:58 -0500
Message-ID: <5.2.1.1.0.20060107154638.04237ad0@pop6.sympatico.ca> (raw)
In-Reply-To: <43BFE657.7050303@fuzzy.cz>
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....
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: how to transform list to table and evaluate an
In-Reply-To: <5.2.1.1.0.20060107154638.04237ad0@pop6.sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox