From: | tvees(at)davincigroep(dot)nl |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #8441: Recursive function in plpgsql does not seem to handle null values correctly |
Date: | 2013-09-09 13:18:59 |
Message-ID: | E1VJ1Mp-0003Bd-UZ@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2013-09-09 15:26:48 | Re: BUG #8441: Recursive function in plpgsql does not seem to handle null values correctly |
Previous Message | sabat.jitendra20 | 2013-09-09 07:55:14 | BUG #8440: sevices not started automatically |