String REPLACE function - Mailing list pgsql-sql

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.

Am I missing anything? Has anyone already solved this?

Thanks!

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington