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

String Translation

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: (view raw, whole thread or download thread mbox)
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 

     my $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 Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394                     Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


sfpug by date

Next:From: Josh BerkusDate: 2003-10-16 17:05:29
Subject: Re: String Translation
Previous:From: Sean ChittendenDate: 2003-10-15 17:33:36
Subject: Re: running postgres in a ramdisk

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