From: | elein <elein(at)varlena(dot)com> |
---|---|
To: | David Wheeler <david(at)kineticode(dot)com> |
Cc: | sfpug(at)postgresql(dot)org |
Subject: | Re: String Translation |
Date: | 2003-10-16 17:30:57 |
Message-ID: | 20031016103057.C21407@cookie.varlena.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
The basic SQL is this:
update <table>
set <column>=fixident(<column>)
where <column> = <column>;
If the table were "contrib" and the column in question
was "email", do:
update contrib set email=fixident(email) where email=email;
And where fixident(text) is the perl function
create or replace function fixident (text)
returns text as
'
my $data = $_[0];
$data =~ y/a-z0-9/_/cs;
return $data;
' language 'plperl';
This should be faster. You don't need to fetch
the value of the keyname before hand.
On Wed, Oct 15, 2003 at 05:21:14PM -0700, David Wheeler wrote:
> 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 | David Wheeler | 2003-10-16 17:39:05 | Re: String Translation |
Previous Message | Josh Berkus | 2003-10-16 17:05:29 | Re: String Translation |