Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Ross J. Reedstrom" <reedstrm@rice.edu>, pgsql-sql@postgresql.org
Subject: Re: counting text matches - any recipes?
Date: Mon, 4 Nov 2002 15:26:12 -0800
References: <20021104225320.GA6001@wallace.ece.rice.edu>
In-Reply-To: <20021104225320.GA6001@wallace.ece.rice.edu>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211041526.12931.josh@agliodbs.com>


Ross,

> For the basic interface, we have the user types some words into a textbox,
> then just search 'everything'. I'm weighting the results, so that hits
> on the title count more than on keywords, which count more than on the
> abstract, which count more than in the body.

Before you re-invent the wheel, have you checked out OpenFTS?  www.openfts.=
org=20
(I think).
>=20
> This is working reasonably well, but I'm stuck on one counting problem:
> what's the natural way to count multiple matches in SQL?

Within a single text field?  There isn't.

2 choices:

1) Use a Full Text Searching engine, such as the simple one in /contrib or =
a=20
more full-featured one like OpenFTS.   Both will allow you to do counts on=
=20
"hits" for a keyword.

2) Write a function in PL/perl which will count the number of keyword match=
es=20
in a text string. Potentially slower than #1, but easier to implement.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco