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

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

Worksforme. You sure you transcribed the function accurately?

Note however that sufficiently long input strings *will* drive this
function to stack overrun, if you don't run out of heap memory first
(I think the heap consumption will be O(N^2) ...). Consider rewriting
it with a loop rather than recursion.

regards, tom lane