Re: levenshtein contrib installation

Arnaud Lesauvage <thewild@freesurf.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