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(at)davincigroep(dot)nl>

> The following bug has been logged on the website:
>
> Bug reference: 8441
> Logged by: Tom van Ees
> Email address: tvees(at)davincigroep(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>