From: | David Wheeler <david(at)kineticode(dot)com> |
---|---|
To: | sfpug(at)postgresql(dot)org |
Subject: | String Translation |
Date: | 2003-10-16 00:21:14 |
Message-ID: | F05A5E6F-FFF9-11D7-9D1B-0003931A964A@kineticode.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
Hi All,
I have an upgrade script for the forthcoming version of Bricolage. It
has a function that looks like this:
sub update_all {
my ($table) = @_;
my $get_name = prepare("SELECT name FROM $table");
my $set_key_name = prepare("UPDATE $table SET key_name=? WHERE
name=?");
my $name;
execute($get_name);
bind_columns($get_name, \$name);
while (fetch($get_name)) {
my $key_name = lc($name);
$key_name =~ y/a-z0-9/_/cs;
execute($set_key_name, $key_name, $name);
}
}
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.
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.
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?
Many TIA,
David
--
David Wheeler AIM: dwTheory
david(at)kineticode(dot)com ICQ: 15726394
http://www.kineticode.com/ Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2003-10-16 17:05:29 | Re: String Translation |
Previous Message | Sean Chittenden | 2003-10-15 17:33:36 | Re: running postgres in a ramdisk |