8.0.3 regexp_replace()...

From: "rlee0001" <robeddielee(at)hotmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: 8.0.3 regexp_replace()...
Date: 2006-01-30 17:49:05
Message-ID: 1138643345.468079.260290@g49g2000cwa.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message rlee0001 2006-01-30 17:52:09 Re: Little Offtopic: Database frontends
Previous Message Doug McNaught 2006-01-30 17:11:55 Re: How to change the default database for a user