Re: REGEXP_REPLACE woes

For the record: I've got two different flavors of those "shortlinks".
The first one, [p=123|John Smith] is the one that I started this thread
with. The second one is just a person number like [p=123] and should be
expanded to a similar link, with the default person name (fetched by
get_person_name(123)) inserted.

Here's my full function that expands both kinds of shortlinks:

CREATE OR REPLACE FUNCTION link_expand(TEXT) RETURNS TEXT AS $$
DECLARE
str TEXT;
tmp TEXT;
name TEXT;
p INTEGER;
BEGIN
-- the easy part: replace [p=xxx|yyy] with full link
str := REGEXP_REPLACE($1,
E'\\[p=(\\d+?)\\|(.+?)\\]',
E'<a href="./family.php?person=\\1">\\2</a>', 'g');
-- the hard part: replace [p=xxx] with full link
WHILE str SIMILAR TO E'%\\[p=\\d+\\]%' LOOP
str := REGEXP_REPLACE(str,
E'\\[p=(\\d+?)\\]',
E'<a href="./family.php?person=\\1">#\\1#</a>');
tmp := SUBSTRING(str, E'#\\d+?#');
p := BTRIM(tmp, '#')::INTEGER;
name := get_person_name(p);
str := REPLACE(str, tmp, name);
END LOOP;
RETURN str;
END
$$ LANGUAGE plpgsql STABLE;

I still think that "the hard part" is a bit ugly, though.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/