Re: 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(dot)le0n(at)gmail(dot)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

> Il giorno 08/feb/2012, alle ore 11:25, Marti Raudsepp <marti(at)juffo(dot)org> ha scritto:
>
>> On Wed, Feb 8, 2012 at 01:17, 84.le0n <84(dot)le0n(at)gmail(dot)com> wrote:
>>>> 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.
>>>
>>> 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 is
>>>>> for other major databases ?
>>>>
>>>> Unfortunately the chances are pretty slim, the last CommitFest for
>>>> PostgreSQL 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.

Regards,
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(dot)le0n(at)gmail(dot)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