fuzzystrmatch -

Levenshtein and Metaphone fuzzy string matching

by Joe Conway <joseph.conway@home.com>

by Joe Conway <joseph.conway@home.com>, Joel Burton <jburton@scw.org>

intarray -

Index support for arrays of int4, using GiST

metaphone -

Improved Soundex function

by Joel Burton <jburton@scw.org>

miscutil -

PostgreSQL assert checking and various utility functions

Confidence-interval datatype (GiST indexing example)

by Gene Selkov, Jr. <selkovjr@mcs.anl.gov>

soundex -

Soundex function

spi -

Various trigger functions, examples for using SPI.

* Metaphone was originally created by Lawrence Philips and presented in article

* in "Computer Language" December 1990 issue.

*

* soundex()

* -----------

* Folded existing soundex contrib into this one. Renamed text_soundex() (C function)

* to soundex() for consistency.

*

* Permission to use, copy, modify, and distribute this software and its

* documentation for any purpose, without fee, and without a written agreement

* is hereby granted, provided that the above copyright notice and this

*/

Version 0.1 (3 August, 2001):

Version 0.2 (7 August, 2001):

Functions to calculate the degree to which two strings match in a "fuzzy" way

Tested under Linux (Red Hat 6.2 and 7.0) and PostgreSQL 7.2devel

Release Notes:

Version 0.2

- folded soundex contrib into this one

Version 0.1

- initial release

NOTE: Modified August 07, 2001 by Joe Conway. Updated for accuracy

after combining soundex code into the fuzzystrmatch contrib

---------------------------------------------------------------------

The Soundex system is a method of matching similar sounding names

(or any words) to the same code. It was initially used by the

United States Census in 1880, 1900, and 1910, but it has little use

beyond English names (or the English pronunciation of names), and

it is not a linguistic tool.

The following are some usage examples:

SELECT soundex('hello world!');

CREATE TABLE s (nm text)\g

insert into s values ('john')\g

insert into s values ('joan')\g

insert into s values ('wobbly')\g

select * from s

where soundex(nm) = soundex('john')\g

select nm from s a, s b

where soundex(a.nm) = soundex(b.nm)

and a.oid <> b.oid\g

CREATE FUNCTION text_sx_eq(text, text) RETURNS bool AS

'select soundex($1) = soundex($2)'

LANGUAGE 'sql'\g

CREATE FUNCTION text_sx_lt(text,text) RETURNS bool AS

'select soundex($1) < soundex($2)'

LANGUAGE 'sql'\g

CREATE FUNCTION text_sx_gt(text,text) RETURNS bool AS

'select soundex($1) > soundex($2)'

LANGUAGE 'sql';

CREATE FUNCTION text_sx_le(text,text) RETURNS bool AS

'select soundex($1) <= soundex($2)'

LANGUAGE 'sql';

CREATE FUNCTION text_sx_ge(text,text) RETURNS bool AS

'select soundex($1) >= soundex($2)'

LANGUAGE 'sql';

CREATE FUNCTION text_sx_ne(text,text) RETURNS bool AS

'select soundex($1) <> soundex($2)'

LANGUAGE 'sql';

DROP OPERATOR #= (text,text)\g

CREATE OPERATOR #= (leftarg=text, rightarg=text, procedure=text_sx_eq,

commutator=text_sx_eq)\g

SELECT *

FROM s

WHERE text_sx_eq(nm,'john')\g

SELECT *

from s

where s.nm #= 'john';

return(META_SUCCESS);

}/* END metaphone */

PG_FUNCTION_INFO_V1(soundex);

soundex(PG_FUNCTION_ARGS)

{

charoutstr[SOUNDEX_LEN+1];

char*arg;

arg=_textout(PG_GETARG_TEXT_P(0));

_soundex(arg,outstr);

PG_RETURN_TEXT_P(_textin(outstr));

}

_soundex(constchar*instr,char*outstr)

{

intcount;

AssertArg(instr);

AssertArg(outstr);

outstr[SOUNDEX_LEN]='\0';

while (!isalpha((unsignedchar)instr[0])&&instr[0])

++instr;

if (!instr[0])

{

outstr[0]= (char)0;

return;

}

*outstr++= (char)toupper((unsignedchar)*instr++);

count=1;

while (*instr&&count<SOUNDEX_LEN)

{

if (isalpha((unsignedchar)*instr)&&

soundex_code(*instr)!=soundex_code(*(instr-1)))

{

*outstr=soundex_code(instr[0]);

if (*outstr!='0')

{

++outstr;

++count;

}

}

++instr;

}

while (count<SOUNDEX_LEN)

{

*outstr='0';

++outstr;

++count;

}

}

#defineMAX_LEVENSHTEIN_STRLEN255

#defineMAX_METAPHONE_STRLEN255

{

intvalue;

}dynmat;

externDatumlevenshtein(PG_FUNCTION_ARGS);

externDatummetaphone(PG_FUNCTION_ARGS);

externDatumsoundex(PG_FUNCTION_ARGS);

staticvoid_soundex(constchar*instr,char*outstr);

#defineSOUNDEX_LEN 4

#define_textin(str) DirectFunctionCall1(textin, CStringGetDatum(str))

#define_textout(str) DatumGetPointer(DirectFunctionCall1(textout, PointerGetDatum(str)))

staticconstchar*soundex_table="01230120022455012623010202";

#definesoundex_code(letter) soundex_table[toupper((unsigned char) (letter)) - 'A']

#defineSTRLEN(p) strlen(p)

#defineCHAREQ(p1,p2) (*(p1) == *(p2))

#defineNextChar(p) ((p)++)

#defineMAX_LEVENSHTEIN_STRLEN255

#defineMAX_METAPHONE_STRLEN255

CREATE FUNCTION metaphone (text,int) RETURNS text

AS 'MODULE_PATHNAME','metaphone' LANGUAGE 'c' with (iscachable, isstrict);

CREATE FUNCTION soundex(text) RETURNS text

AS 'MODULE_PATHNAME', 'soundex' LANGUAGE 'c' with (iscachable, isstrict);

CREATE FUNCTION text_soundex(text) RETURNS text

AS 'MODULE_PATHNAME', 'soundex' LANGUAGE 'c';