Обсуждение: Indexing Metaphone?
Folks, I'm building a database that does a lot of fuzzy-string searches. As such, I'd like to index the metaphone results of certain columns. However, I get this when I try: jwnet=> CREATE INDEX idx_locations_metaphone ON locations(metaphone(location_name, 12)); ERROR: parser: parse error at or near "12" jwnet=> is this a syntax error, or do I need to create a column to hold the metaphone values in order to index them? -- -Josh Berkus
On Wed, Jun 05, 2002 at 11:12:02AM -0700, Josh Berkus wrote: > jwnet=> CREATE INDEX idx_locations_metaphone ON > locations(metaphone(location_name, 12)); > ERROR: parser: parse error at or near "12" > jwnet=> > > is this a syntax error, or do I need to create a column to hold the metaphone > values in order to index them? That's the error message you'd get if pg just didn't know of any function called "metaphone". Have you done the appropriate CREATE FUNCTION magic? "\df metaphone" in psql will tell you whether it's heard of it or not. Richard
Richard, > That's the error message you'd get if pg just didn't know of any > function called "metaphone". Have you done the appropriate CREATE > FUNCTION magic? "\df metaphone" in psql will tell you whether it's > heard of it or not. Yup: jwnet=> \df metaphone List of functionsResult data type | Name | Argument data types ------------------+-----------+---------------------text | metaphone | text, integer (1 row) -- -Josh Berkus
On Wed, 5 Jun 2002, Josh Berkus wrote: > Folks, > > I'm building a database that does a lot of fuzzy-string searches. As such, > I'd like to index the metaphone results of certain columns. However, I get > this when I try: > > jwnet=> CREATE INDEX idx_locations_metaphone ON > locations(metaphone(location_name, 12)); > ERROR: parser: parse error at or near "12" Functional indexes only take columns as arguments IIRC. Make a cachable function that returns metaphone($1, 12) given the appropriate type and index/use that. :)