I have a stupid problem. My server is running an old version of postgres (8.0.3) and therefore lacks the regexp_replace() function. It does however support substring and replace functions. So what I am trying to do is emulate the regexp_replace() function by creating a function which finds each matching substring and replaces them by hand in a loop. The loop is supposed to exit when there are no more matches to replace. The problem is that the function enters and infinate loop which brings down the server. The faulty code follows: -- PRE-PostgreSQL 8.1 regexp_replace() function called regexp_replacex() CREATE OR REPLACE FUNCTION "regexp_replacex" (source varchar, pattern varchar, replacement varchar) RETURNS varchar AS $body$ DECLARE retvalue VARCHAR; BEGIN retvalue = "source"; LOOP retvalue = REPLACE(retvalue, SUBSTRING(retvalue FROM "pattern"), "replacement"); EXIT WHEN retvalue = REPLACE(retvalue, SUBSTRING(retvalue FROM "pattern"), "replacement"); END LOOP; RETURN retvalue; END; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Now first of all I realize that my code is stupid and the problem is stupid and if it were up to be I'd be running 8.1 anyway. But its not up to me but I really need this functionality. The parameters might be in a different order than the real 8.1 regexp_replace so pay attention if you test it and do expect to have to kill the server when it enters the infinate loop. I can not beleave that nobody has done this before and yet I can't find it anywhere on the net. I've been searching Google and Google Groups for "postgres replace substring return function" and nothing. Can anyone spot my folly?
