Re: Re: Metaphone function attachment - Mailing list pgsql-general
From | Bruce Momjian |
---|---|
Subject | Re: Re: Metaphone function attachment |
Date | |
Msg-id | 200105071558.f47Fwsa08998@candle.pha.pa.us Whole thread Raw |
In response to | Re: Metaphone function attachment (mlw <markw@mohawksoft.com>) |
List | pgsql-general |
Sure, send them over and we can put them in /contrib. Send them to the patches list, I think. > Joel Burton wrote: > > > > -- > > Joel Burton <jburton@scw.org> > > Director of Information Systems, Support Center of Washington > > > > ------------------------------------------------------------------------------- > > Name: contrib-metaphone.tgz > > contrib-metaphone.tgz Type: unspecified type (APPLICATION/octet-stream) > > Encoding: BASE64 > > > > ------------------------------------------------------------------------------- > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > I have written a similar function, and it is in a library I wrote "pgcontains" > which started out as a simple implementation of "contains(...)" but has grown > WAY beyond it's original scope. > > Anyway, Metaphone is great for doing some cool searches, but now that we are on > the subject of cool search functions, I have a few that may be useful and would > be glad to contribute. My only question is how? (and I'd have to write them for > 7.1, because they are still in 7.0.x format) > > > contains(...) > A simple implementation of contains. Forces a table scan, but does some cool > things including phrase detection. > > decode(...) > Similar to "case" but works as decode for oracle queries. In 7.1 and higher it > should be easy to make one function take a variable number of parameters. Right > now I have a stub for the most common numbers. > > strip(...) > Strips out all but alphanumeric characters and returns a lowercase string. > "Oops I did it again" comes back as "oopsididitagain." This is cool for lightly > fuzzy searches. > > striprev(...) > Like strip, but reverses the string. Allows you to use an index for records > which end in something. For instance: "select * from table where field like > 'abc%'" can use an index, where as "select * from table where field like > '%xyx'" will not. However, "select * from table where striprev(field) like > striprev('xyz') || '%'" can. > > Example: > cdinfo=# select title, striprev(title) from ztitles where striprev(title) like > striprev('wall') || '%' limit 3; > title | striprev > --------------------------------------------+------------------------------------ > A Giggle Can Wiggle Its Way Through A Wall | > llawahguorhtyawstielggiwnacelggiga > Shadows On A Wall * | llawanoswodahs > The Wall | llaweht > (3 rows) > > cdinfo=# explain select title, striprev(title) from ztitles where > striprev(title) like striprev('wall') || '%' limit > 3; > NOTICE: QUERY PLAN: > > Limit (cost=0.00..10.21 rows=3 width=12) > -> Index Scan using f1 on ztitles (cost=0.00..7579.94 rows=2227 width=12) > > EXPLAIN > > int64_t *strtonumu(text, int4 base) > Converts a string to a number with an arbitrary base. (Is there a function to > do this already?) > > > -- > I'm not offering myself as an example; every life evolves by its own laws. > ------------------------ > http://www.mohawksoft.com > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
pgsql-general by date: