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/