Re: search and replace

Gallamine,

> I have a database with text in it (articles) and in the article I have <img>
> tags for pictures in HTML. I want to go through the DB and replace the urls
> of the images. How would I go about doing that? I want to grab all the text
> from the articles table and replace the string "http://dopey.lulupress.com"
> with "http://faramir/".
> Any hints?

Here's what I did:

1) compiled Postgres with Perl support (--with-perl)
2) createlang plperl (from the command line, as postgres)
3) Created this simple Perl function:

CREATE FUNCTION strswap(
TEXT, VARCHAR, VARCHAR )
RETURNS TEXT AS '
my($the_text, $look_up, $replace_with) = @_;
$the_text =~ s:$look_up:$replace_with:eg ;
return $the_text;
' LANGUAGE 'plperl' WITH (ISCACHABLE, ISSTRICT);

Then you can use strswap to swap all of this stuff out.

--
-Josh Berkus
Aglio Database Solutions
San Francisco