I did get the code working. The function DDL follows:
CREATE OR REPLACE FUNCTION "webadmin"."regexp_replacex" (source
varchar, pattern varchar, replacement varchar) RETURNS varchar AS
$body$
DECLARE
retvalue VARCHAR;
BEGIN
retvalue = "source";
LOOP
retvalue = REPLACE(retvalue, COALESCE(SUBSTRING(retvalue FROM
"pattern"), ''), "replacement");
EXIT WHEN retvalue = REPLACE(retvalue, COALESCE(SUBSTRING(retvalue
FROM "pattern"), ''), "replacement");
END LOOP;
RETURN retvalue;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
The problem was that SUBSTRING returns NULL if it cannot find any
matches for the pattern and when the second parameter to REPLACE
returns NULL, REPLACE returns NULL (which is idiotic). Using COALESCE I
ensure that is SUBSTRING cannot find a match that '' (empty string) is
sent to REPLACE. REPLACE then behaves as expected and replaces nothing.
Debugging PostgreSQL's retarded behaviour around NULL values can be a
real pain. But at least I learned to use EMS PostgreSQL Manager for
Windows' function debugger, which can step through a function while
reporting the values of all variables, parameters and return values.
Very handy.
-Robert
