On Mon, 30 Jan 2006, rlee0001 wrote: > 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). Why do you say that? I'd say that's precisely the most reasonable answer. You're asking to replace an unknown portion of a string (since NULL is unknown, it might match some portion of the string, it might not) with something else. The answer to that seems pretty unknown to me.
