Skip site navigation (1) Skip section navigation (2)

Re: String Translation

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: David Wheeler <david(at)kineticode(dot)com>, sfpug(at)postgresql(dot)org
Subject: Re: String Translation
Date: 2003-10-16 17:05:29
Message-ID: 200310161005.29465.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
David,

> I have an upgrade script for the forthcoming version of Bricolage. It
> has a function that looks like this:

So your purpose is to eliminate all non-[0-9A-Za-z_] characters?

> Now, this is all well and good, except that it takes _forever_ to run!
> I have it running now on a database with two tables to be updated, with
> a total of 2213 rows, and it has been running for about 30 mins. And
> yes, it's a fast server with a reasonably tuned database.

That's rather odd ... I've done search-and-replace using Perl on 30,000 large 
text fields in a couple minutes.    Does this field have foriegn keys and/or 
several indexes on it?

> Now, I could speed this up if it could just run in a single query,
> rather than with a big select and 2213 UPDATEs. The trouble is, I don't
> know of an SQL equivalent to y/a-z0-9/_/cs, which translates any non
> alphanumeric characters to underscores. I see that there's a
> substring() function that supports POSIX regular expressions, but that
> doesn't do a substitution.

Well, the new substring can do substitutions, but not neatly in the s//g 
format the way Perl can.   What about PL/perl?

> So my question is, does anyone know of a way I could do this in the
> database (and it is a one-time change [I hope!])? And if there is one,
> will it be faster?

Not necessarily.  I think your execution time problem is not releated to the 
queries themselves, but to some major database dependency.

Wanna show me, since I'll soon be getting paid to tune Bric anyway?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

sfpug by date

Next:From: eleinDate: 2003-10-16 17:30:57
Subject: Re: String Translation
Previous:From: David WheelerDate: 2003-10-16 00:21:14
Subject: String Translation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group