Re: levenshtein contrib installation

Hi List !

I am running Postgresql 8.1.3 on a Win32 box (from binaries).
I read on the list that there was a contrib by Joseph Conway to
implement the Levenshtein distance algorithm.
Could anyone tell me if it is easy to install such a contrib in
postgresql, and if it is, how to do it ?
I suspect there is some kind of compilation needed, but maybe I am
wrong ?

Thanks a lot !

--
Arnaud

Arnaud Lesauvage <thewild(at)freesurf(dot)fr> writes:

> I am running Postgresql 8.1.3 on a Win32 box (from binaries).
> I read on the list that there was a contrib by Joseph Conway to
> implement the Levenshtein distance algorithm.
> Could anyone tell me if it is easy to install such a contrib in
> postgresql, and if it is, how to do it ?
> I suspect there is some kind of compilation needed, but maybe I am
> wrong ?

If it isn't part of pginstaller, I'm afraid so. The environment needed
on win32 for building contrib modules should be the same as for
postgresql itself, which is documented here:
<http://www.postgresql.org/docs/8.1/static/installation.html>

If you don't need the fastest possible implementation, you could as
well use a PL/pgSQL version:

--8<---------------cut here---------------start------------->8---
create or replace function plpgsql_edit_distance(stra text, strb text)
returns integer as $$
declare
rows integer;
cols integer;
begin
rows := length(stra);
cols := length(strb);

IF rows = 0 THEN
return cols;
END IF;
IF cols = 0 THEN
return rows;
END IF;

declare
row_u integer[];
row_l integer[];
diagonal integer;
upper integer;
left integer;
begin
FOR i in 0..cols LOOP
row_u[i] := i;
END LOOP;

FOR i IN 1..rows LOOP
row_l[0] := i;
FOR j IN 1..cols LOOP
IF substring (stra, i, 1) = substring (strb, j, 1) THEN
diagonal := row_u[j-1];
else
diagonal := row_u[j-1] + 1;
END IF;
upper := row_u[j] + 1;
left := row_l[j-1] + 1;
row_l[j] := int4smaller(int4smaller(diagonal, upper), left);
END LOOP;
row_u := row_l;
END LOOP;
return row_l[cols];
end;
end
$$ language 'plpgsql' immutable strict;
--8<---------------cut here---------------end--------------->8---

HTH
Andreas

Andreas Seltenreich a écrit :
> Arnaud Lesauvage <thewild(at)freesurf(dot)fr> writes:
>
>> I am running Postgresql 8.1.3 on a Win32 box (from binaries).
>> I read on the list that there was a contrib by Joseph Conway to
>> implement the Levenshtein distance algorithm.
>> Could anyone tell me if it is easy to install such a contrib in
>> postgresql, and if it is, how to do it ?
>> I suspect there is some kind of compilation needed, but maybe I am
>> wrong ?
>
> If it isn't part of pginstaller, I'm afraid so. The environment needed
> on win32 for building contrib modules should be the same as for
> postgresql itself, which is documented here:
> <http://www.postgresql.org/docs/8.1/static/installation.html>
>
> If you don't need the fastest possible implementation, you could as
> well use a PL/pgSQL version:

Hi Andreas !

The PL/pgSQL version you posted is just perfect ! Thanks a lot !

--
Arnaud

Our software will be using PostGreSQL as a database. Now I was wondering, if
the database is installed on lets say an entry level Celeron, with 256MB of
Ram, will it slow down the PC at all?

I'm not taking any queries into account here, just generally, does
installing the database slow down ones PC a bit? Unfortunately I don't have
access to such an entry-level PC, so I can't test it.

Thanks