Re: String REPLACE function

Hi Joel,

Joel Burton schrieb:
>
> Is there a function for substring replacement?
>
> There's translate(s, a, b), but that replaces all characters in a with
> their corresponding character in b, eg.
>
>   replace ('this is a cat', 'cat', 'dog') => ghis is o dog
>
> I'm looking for a function that matches the whole string and replaces it:
>
>   replace ('this is a cat', 'cat', 'dog') => this is a dog
>
> I know I could write it in PL/PGSQL, but it seems that it would be very
> inefficient. We're not using PL/perl or PL/tcl in this project, so I'd
> rather not do it this way if it coulod be avoided.

As long as you rebuild this functionality with native SQL and/or
PGSQL functions performance should not be a problem at all. Have
a look at this code example which uses plpgsql as language.

CREATE FUNCTION stuff (text, text, text)
RETURNS text
AS '
    DECLARE
        source ALIAS FOR $1;
        search ALIAS FOR $2;
        newstr ALIAS FOR $3;
        prefix text;
        postfix text;
        pos integer;
        len_of_search integer;

    BEGIN
        pos := position(search in source);
        IF pos <= 0 THEN
            RETURN source;
        END IF;

        len_of_search := char_length(search);
        prefix := substring(source from 1 for pos - 1);
        postfix := substring(source from pos + len_of_search);

        RETURN textcat(textcat(prefix, newstr), postfix);
    END;
' LANGUAGE 'plpgsql'

I have made a very simple test to give you an idea of the
performance. I ran

    update dt set d = stuff(d, 'dog', 'cat') where d notnull;

on a table with 4096 records of 'this is a dog'. With replacement
output of

    time psql test -c "update dt set d = stuff(d, 'dog', 'cat') where d notnull; "

realtime was about 5.8s, without replacement about 1.9s. This on
my now ancient Pentium clone (200MHz IDT Winchip, 72MB RAM). I
think this is not so bad for interpreted PL.

>
> Am I missing anything? Has anyone already solved this?
>
> Thanks!

Hope this helps.

> ...

Martin

--
Dipl-Ing. Martin Jacobs * Windsbach * Germany
Registered Linux User #87175, http://counter.li.org/