Re: BUG #8441: Recursive function in plpgsql does not seem to handle null values correctly

Hello

it works on 9.1.9

postgres=# SELECT longlevenshtein(null, 'foobar');
 longlevenshtein
-----------------
               6


Regards

Pavel

P.S. unlimitted varchar is "text" type in Postgres


2013/9/9 <tvees@davincigroep.nl>

> The following bug has been logged on the website:
>
> Bug reference:      8441
> Logged by:          Tom van Ees
> Email address:      tvees@davincigroep.nl
> PostgreSQL version: 9.0.4
> Operating system:   Windows Server 2008 R2
> Description:
>
> The Levenshtein function can only handle strings with length 255 or less.
> I needed a Levenshtein function that could handle longer strings.
> Therefore I wrote the following udf:
>
>
> CREATE OR REPLACE FUNCTION longlevenshtein (string1 character varying
> (1000000), string2 character varying (1000000)) RETURNS integer AS $$
>     BEGIN
>         IF  (length(coalesce($1, '')) = 0 AND length(coalesce($2, '')) =
> 0) THEN
>             RETURN 0;
>         ELSEIF ($1 IS NULL and $2 IS NOT NULL and length($2) > 0) THEN
>             RETURN length($2);
>         ELSEIF ($2 IS NULL and $1 IS NOT NULL and length($1)> 0) THEN
>             RETURN length($1);
>         ELSEIF length($1) = 0  AND length(coalesce($2, '')) > 0 THEN
>             RETURN length(coalesce($2, ''));
>         ELSEIF length($1) > 0 AND (length($2) = 0 or $2 is null) THEN
>             RETURN length(coalesce($1, ''));
>         ELSE
>             RETURN (Levenshtein(SUBSTRING($1 FROM 1 FOR 254), SUBSTRING($2
> FROM 1
> for 254)) + longlevenshtein(coalesce(SUBSTRING($1 FROM 255), ''),
> coalesce(SUBSTRING($2 FROM 255), '')));
>         END IF;
>     END;
> $$ LANGUAGE plpgsql;
>
>
> When I invoke this function with
> SELECT longlevenshtein(null, 'foobar')
> I get a ERROR:  stack depth limit exceeded
> while I expected the return value 6
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>