Обсуждение: SOUNDEX call
Hi all, I'm developing Joomla's PostgreSQL database driver that (I hope) will be included soon in this CMS [1] [2]. During my developing time I've found a big problem, not yet resolved, about SOUNDEX function, adopted for recent added code. I know that SOUNDEX is present inside contrib module, but to be installed is required database's admin privilege to be run and, usually during a CMS installation on a remote shared server, this privilege is not given. Now the problem is, how can I enable SOUNDEX inside database ? Is there a way to use it without running contrib files ? Can I hope on SOUNDEX inclusion inside core for next release, as is for other major databases ? Thank you for your job! [1] https://github.com/gpongelli/joomla-platform/tree/postgresql [2] https://github.com/gpongelli/joomla-cms/tree/postgresql ps I'm not a user of this mailing list, please add me to Cc. Eng. Gabriele Pongelli
On Mon, Feb 6, 2012 at 23:48, 84.le0n <84.le0n@gmail.com> wrote: > Now the problem is, how can I enable SOUNDEX inside database ? Is > there a way to use it without running contrib files ? This is a bit ugly, but you could write the function yourself in the PL/pgSQL procedural language, which is enabled by default in Postgres 9.0+. Of course this would be slower. > Can I hope on SOUNDEX inclusion inside core for next release, as is > for other major databases ? Unfortunately the chances are pretty slim, the last CommitFest for PostgreSQL 9.2 closed mid-January. Regards, Marti
On Wed, Feb 8, 2012 at 01:17, 84.le0n <84.le0n@gmail.com> wrote:This is a bit ugly, but you could write the function yourself in thePL/pgSQL procedural language, which is enabled by default in Postgres9.0+. Of course this would be slower.You answered yourself, this would be slower, I'll not do this.
How are you actually using soundex() in queries? If you just call it
once or twice with short keywords, it probably won't make a noticeable
difference. And you would gain a lot in compatibility (it will take
ages for hosting providers to upgrade to 9.3).
If you have queries like
WHERE soundex(somecolumn)=soundex('foobar')
then creating an index over (soundex(somecolumn)) would remove most of
the function overhead.
Yes, that queries are similar to your example, I need to use SOUNDEX inside WHERE clause and in INSERT INTO query.
Surely I'll add an index on that column, but I haven't SOUNDEX call.
You can provide the PL/pgSQL function by default as a fallback, and if
it turns out to be too slow for some users, you can tell them to
enable the fuzzystrmatch extension optionally. Make it work first,
then make it fast.
This is an interesting solution, but I know a little bit PL/pgSQL and I don't know how provide SOUNDEX version in PL/pgSQL, I don't know SOUNDEX algo too.
How can I provide soundex in
PL/pgSQL ?
Can I hope on SOUNDEX inclusion inside core for next release, as isfor other major databases ?Unfortunately the chances are pretty slim, the last CommitFest forPostgreSQL 9.2 closed mid-January.When will be next?
Don't count on this. 9.3 will be released no earlier than 2013 June.
And it's not certain that making it a core function will be accepted
either.
I think I'll follow your help.
Eng. Gabriele Pongelli
AVVERTENZE AI SENSI DEL D.LGS. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e negli eventuali files allegati, sono da considerarsi strettamente riservati. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceveste per errore questo messaggio, Vi preghiamo cortesemente di darcene notizia all'indirizzo e-mail di cui sopra e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema; costituisce comportamento contrario ai principi dettati dal D.lgs. 196/2003 il trattenere il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse.
This electronic transmission is strictly confidential and intended solely for the addresses. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify us and delete the received data as soon as possible.
On Wed, Feb 8, 2012 at 15:37, 84.le0n <84.le0n@gmail.com> wrote: > This is an interesting solution, but I know a little bit PL/pgSQL and I > don't know how provide SOUNDEX version in PL/pgSQL, I don't know SOUNDEX > algo too. > How can I provide soundex in PL/pgSQL ? I wrote and posted a PL/pgSQL version of soundex() on the PostgreSQL wiki: https://wiki.postgresql.org/wiki/Soundex You are free to use it under the terms of any version of the GPL license, MIT license or PostgreSQL license at your choice. If you end up using it, getting some credit in release notes would be appreciated. :) Regards, Marti